﻿/*ESRPConnectionString*/
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
/*Modificari pana la data de 01.01.2011*/
/**/


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ExBugSetup' AND COLUMN_NAME = 'ColoanaDepartamente')
BEGIN
	ALTER TABLE dbo.ExBugSetup ADD ColoanaDepartamente bit NOT NULL DEFAULT(1)
END
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Departamente' AND COLUMN_NAME = 'Ordine')
BEGIN
	ALTER TABLE dbo.Departamente ADD Ordine smallint NOT NULL DEFAULT(0)
END
GO



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExecutieCheltuieliPacienti]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ExecutieCheltuieliPacienti](
	[IDExecCheltPacienti] [int] IDENTITY(1,1) NOT NULL,
	[IDDepartament] [int] NOT NULL,
	[IDCasaAsigSanatate] [smallint] NOT NULL,
	[Data] [datetime] NOT NULL,
	[Internati] [decimal](18, 0) NOT NULL  DEFAULT ((0)),
	[ZileSpitalizare] [decimal](18, 0) NOT NULL DEFAULT ((0)),
	[IDJurnal] [int] NULL,
	[CodUnitate] [smallint] NOT NULL,
 CONSTRAINT [PK_ExecutieCheltuieliPacienti] PRIMARY KEY CLUSTERED ([IDExecCheltPacienti] ASC),
 CONSTRAINT [FK_ExecutieCheltuieliPacienti_CaseAsigurariSanatate] FOREIGN KEY([IDCasaAsigSanatate])REFERENCES [dbo].[CaseAsigurariSanatate] ([IdCasaAsigSanatate]),
 CONSTRAINT [FK_ExecutieCheltuieliPacienti_Departamente] FOREIGN KEY([IDDepartament])REFERENCES [dbo].[Departamente] ([IDDepartament]),
 CONSTRAINT [FK_ExecutieCheltuieliPacienti_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
 )
end

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exbug_ExecutieCheltuieliPacientiArtAl]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_ExecutieCheltuieliPacientiArtAl]
GO


CREATE PROCEDURE [dbo].[exbug_ExecutieCheltuieliPacientiArtAl]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@Data datetime,
	@IDCasaAsigSanatate varchar(50),
	@IDJurnal int
AS
BEGIN
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO [ExecutieCheltuieliPacienti]
           ([IDCasaAsigSanatate]
           ,[Data]
           ,[Internati]
           ,[ZileSpitalizare]
           ,[CodUnitate]
           ,[IDJurnal]
           ,[IDDepartament])
     SELECT @IDCasaAsigSanatate,@Data,0,0,@CodUnitate,@IDJurnal,IDDepartament  FROM Departamente a WHERE [CodUnitate]=@CodUnitate and a.ExecutieCheltuieli=1 
     AND NOT EXISTS(SELECT * FROM [ExecutieCheltuieliPacienti] e WHERE a.IDDepartament= e.IDDepartament AND a.CodUnitate=e.CodUnitate and MONTH(e.Data)=Month(@Data)and YEAR(e.Data)=YEAR(@Data)  and e.[IDCasaAsigSanatate] = @IDCasaAsigSanatate
     AND e.IDJurnal = @IDJurnal)
     
     DELETE FROM [ExecutieCheltuieliPacienti] WHERE 
     NOT EXISTS(SELECT * FROM Departamente a  WHERE [ExecutieCheltuieliPacienti].IDDepartament= a.IDDepartament AND a.CodUnitate=@CodUnitate AND a.ExecutieCheltuieli=1)
      and MONTH([ExecutieCheltuieliPacienti].Data)=Month(@Data) and YEAR([ExecutieCheltuieliPacienti].Data)=YEAR(@Data) and [ExecutieCheltuieliPacienti].[IDCasaAsigSanatate] = @IDCasaAsigSanatate
     AND [ExecutieCheltuieliPacienti].IDJurnal = @IDJurnal AND [ExecutieCheltuieliPacienti].CodUnitate=@CodUnitate

END

GO

DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [admin_ApplicationMenu] WHERE [ApplicationId]='8489910A-6599-436A-B3E5-21B6DB74A7BE' AND [IDMeniuAplicatie]=2700
IF @Cate= 0 
begin

SET IDENTITY_INSERT [admin_ApplicationMenu] ON
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2700,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Executie Cheltuieli',2087,250,NULL,NULL,NULL)
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2701,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Indicatori pe sectii',2700,1,'Indicatori_Sectii',NULL,NULL)    
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2702,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Cheltuieli toatale pe sectii',2700,2,'Cheltuieli_Totale_Sectii',NULL,NULL)    
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2703,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Cheltuieli pe articole',2700,3,'Cheltuieli_Articole',NULL,NULL)    
SET IDENTITY_INSERT [admin_ApplicationMenu] OFF	
END
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CaseAsigurariSanatate' AND COLUMN_NAME = 'CasaImplicita')
BEGIN
	ALTER TABLE dbo.CaseAsigurariSanatate ADD CasaImplicita bit NOT NULL DEFAULT(0)
END
GO

DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [admin_ApplicationMenu] WHERE [ApplicationId]='8489910A-6599-436A-B3E5-21B6DB74A7BE' AND [IDMeniuAplicatie]=2595
IF @Cate=0 
begin
SET IDENTITY_INSERT [admin_ApplicationMenu] ON
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2595,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Case Asigurari Sanatate',2088,130,'CaseAsigurariSanatate',NULL,NULL)

SET IDENTITY_INSERT [admin_ApplicationMenu] OFF	
END
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exbug_GenereazaExecutieCheltuieliArtAl]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_GenereazaExecutieCheltuieliArtAl]
GO


CREATE PROCEDURE [dbo].[exbug_GenereazaExecutieCheltuieliArtAl]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@Data datetime,
	@ArtAl varchar(50),
	@IDJurnal int
AS
BEGIN
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO [ExecutieCheltuieli]
           ([ArtAl]
           ,[Data]
           ,[Valoare]
           ,[CodUnitate]
           ,[IDJurnal]
           ,[IDDepartament])
     SELECT @ArtAl,@Data,0,@CodUnitate,@IDJurnal,IDDepartament  FROM Departamente a WHERE [CodUnitate]=@CodUnitate and a.ExecutieCheltuieli=1 AND a.Activ=1 
     AND NOT EXISTS(SELECT * FROM [ExecutieCheltuieli] e WHERE a.IDDepartament= e.IDDepartament AND a.CodUnitate=e.CodUnitate and MONTH(e.Data)=Month(@Data)and YEAR(e.Data)=YEAR(@Data)  and e.ArtAl = @ArtAl
     AND e.IDJurnal = @IDJurnal)
     
     DELETE FROM [ExecutieCheltuieli] WHERE 
     NOT EXISTS(SELECT * FROM Departamente a  WHERE [ExecutieCheltuieli].IDDepartament= a.IDDepartament AND a.CodUnitate=@CodUnitate AND a.ExecutieCheltuieli=1 AND a.Activ=1)
      and MONTH([ExecutieCheltuieli].Data)=Month(@Data) and YEAR([ExecutieCheltuieli].Data)=YEAR(@Data) and [ExecutieCheltuieli].ArtAl = @ArtAl
     AND [ExecutieCheltuieli].IDJurnal = @IDJurnal AND [ExecutieCheltuieli].CodUnitate=@CodUnitate

END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exbug_GenereazaExecutieCheltuieliTotal]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_GenereazaExecutieCheltuieliTotal]
GO

CREATE PROCEDURE [dbo].[exbug_GenereazaExecutieCheltuieliTotal]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@Data datetime,
	@ArtAl varchar(40),
	@IDJurnal int
AS
BEGIN
	SET NOCOUNT ON;

     SELECT SUM(Valoare) Total FROM [ExecutieCheltuieli] a WHERE 
     MONTH(a.Data)=Month(@Data) and YEAR(a.Data)=YEAR(@Data) and a.ArtAl = @ArtAl
     AND a.IDJurnal = @IDJurnal AND a.CodUnitate=@CodUnitate
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GenExtrasCont]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GenExtrasCont]
GO


CREATE PROCEDURE [dbo].[balcon_GenExtrasCont]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@Cod varchar(50),
	@DeLaCont dbo.TipCont,
	@PanaLaCont dbo.TipCont,
	@filtruNrNota varchar(8000),
	@filtruJurnal varchar(2000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	IF LEN(@Cod)=0
		SELECT ct.Cont,ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc, 
		ct.DebitInitial - ct.CreditInitial + 
		(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM Rulaje rlj JOIN RulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContDebit = ct.Cont) AND rjd.DataDoc<rd.DataDoc )
		-(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM Rulaje rlj JOIN RulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContCredit = ct.Cont) AND rjd.DataDoc<rd.DataDoc ) 
		 sodlzi,
		SUM(CASE WHEN ruld.ContDebit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajDebit,
		SUM(CASE WHEN ruld.ContCredit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajCredit
		FROM [Conturi] ct 
		JOIN [Rulaje] ruld ON ct.CodUnitate=ruld.CodUnitate AND (ruld.ContDebit = ct.Cont OR ruld.ContCredit=ct.Cont)
		JOIN [RulajeDoc] rd ON ruld.IDDocument = rd.IDDocument
		WHERE ct.CodUnitate = @CodUnitate AND rd.DataDoc<=@PanaLaData AND 
		rd.DataDoc>=@DeLaData AND 
		(LEN(@DeLaCont)=0 OR ct.Cont >= @DeLaCont) AND (LEN(@PanaLaCont) = 0 OR ct.Cont<=@PanaLaCont) AND
		(LEN(LTRIM(RTRIM(@filtruNrNota)))=0 OR (rd.NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@filtruNrNota,',') fart))) AND
		(LEN(LTRIM(RTRIM(@filtruJurnal)))=0 OR (rd.IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@filtruJurnal,',') fart)))
		GROUP BY ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc,ct.Cont
		ORDER BY ct.Cont,rd.DataDoc
	ELSE
		SELECT ct.Cont,ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc, 
		ct.DebitInitial - ct.CreditInitial + 
		(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM Rulaje rlj JOIN RulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContDebit = ct.Cont) AND rjd.DataDoc<rd.DataDoc )
		-(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM Rulaje rlj JOIN RulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContCredit = ct.Cont) AND rjd.DataDoc<rd.DataDoc ) 
		 sodlzi,
		SUM(CASE WHEN ruld.ContDebit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajDebit,
		SUM(CASE WHEN ruld.ContCredit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajCredit
		FROM [Conturi] ct 
		JOIN [ConturiSpeciale] cs ON ct.Cont = cs.Cont AND ct.CodUnitate = cs.CodUnitate AND cs.Tipuri LIKE @Cod
		JOIN [Rulaje] ruld ON ct.CodUnitate=ruld.CodUnitate AND (ruld.ContDebit = ct.Cont OR ruld.ContCredit=ct.Cont)
		JOIN [RulajeDoc] rd ON ruld.IDDocument = rd.IDDocument
		WHERE ct.CodUnitate = @CodUnitate AND rd.DataDoc<=@PanaLaData AND rd.DataDoc>=@DeLaData
		AND
		(LEN(LTRIM(RTRIM(@filtruNrNota)))=0 OR (rd.NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@filtruNrNota,',') fart))) AND
		(LEN(LTRIM(RTRIM(@filtruJurnal)))=0 OR (rd.IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@filtruJurnal,',') fart)))
		GROUP BY ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc,ct.Cont
		ORDER BY ct.Cont,rd.DataDoc
END


GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_UpdateSoldCont]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_UpdateSoldCont]
GO


CREATE PROCEDURE [dbo].[balcon_UpdateSoldCont]
	-- Add the parameters for the stored procedure here
	@codUnitate smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    update Conturi set sold = DebitInitial-CreditInitial+ DebitPrecedent-CreditPrecedent+(SELECT Coalesce(SUM(ValoareLei),0) FROM Rulaje b
	where Conturi.Cont=b.ContDebit and Conturi.CodUnitate=b.Codunitate)
	where Conturi.CodUnitate=@codUnitate AND Conturi.Terminal = 1
	
	update Conturi set sold = sold - (SELECT Coalesce(SUM(ValoareLei),0) FROM Rulaje b
	where Conturi.Cont=b.Contcredit and Conturi.CodUnitate=b.Codunitate)
	where Conturi.CodUnitate=@codUnitate AND Conturi.Terminal = 1

	update Conturi set sold = (SELECT Coalesce(sum(a.sold),0) FROM Conturi a
	where dbo.IsContInclus(Conturi.cont,a.cont,Conturi.Terminal)=1 and Conturi.CodUnitate=a.Codunitate and a.Terminal=1)
	where Conturi.CodUnitate=@codUnitate AND Conturi.Terminal = 0
	


END

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GenNCExtrasCont]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GenNCExtrasCont]
GO


CREATE PROCEDURE [dbo].[balcon_GenNCExtrasCont]
	-- Add the parameters for the stored procedure here
	@cont TipCont,
	@CodUnitate smallint,
	@DeLaData datetime,
	@PanaLaData datetime,
	@An smallint,
	@strFiltruCapitol varchar(2000),
	@strFiltruNrNota varchar(2000)
AS
BEGIN
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.ContDebit, a.ContCredit, 
    b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
    b.TipFormula, c.Categorie, 'D' AS Tip, a.CodUnitate
	FROM  [Rulaje] a 
	INNER JOIN  [RulajeDoc] b ON a.IDDocument = b.IDDocument 
	INNER JOIN  [Conturi] c ON a.ContDebit = c.Cont AND c.CodUnitate = a.CodUnitate
    WHERE [ContDebit]<>'N/A' AND a.CodUnitate = @CodUnitate 
         AND (LEN(@cont)=0 OR a.ContDebit like  @cont + '%')
         AND b.DataDoc >= @DeLaData AND b.DataDoc<= @PanaLaData
         AND (LEN(LTRIM(RTRIM(@strFiltruNrNota)))=0 OR (b.NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruNrNota,',') fart)))
         AND (LEN(LTRIM(RTRIM(@strFiltruCapitol)))=0 OR (b.IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruCapitol,',') fart)))

    UNION 
    SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.[ContCredit] AS [ContDebit], a.[ContDebit] AS ContCredit, 
    b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
    b.TipFormula, c.Categorie, 'C' AS Tip, a.CodUnitate
	FROM  [Rulaje] a 
	INNER JOIN  [RulajeDoc] b ON a.IDDocument = b.IDDocument 
	INNER JOIN  [Conturi] c ON a.ContDebit = c.Cont AND c.CodUnitate = a.CodUnitate
	WHERE [ContCredit]<>'N/A' AND a.CodUnitate = @CodUnitate
		AND (LEN(@cont)=0 OR a.ContCredit like  @cont + '%')
         AND b.DataDoc >= @DeLaData AND b.DataDoc<= @PanaLaData
         AND (LEN(LTRIM(RTRIM(@strFiltruNrNota)))=0 OR (b.NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruNrNota,',') fart)))
         AND (LEN(LTRIM(RTRIM(@strFiltruCapitol)))=0 OR (b.IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruCapitol,',') fart)))
    ORDER BY b.DataDoc

END
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DepartamenteParametrii]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DepartamenteParametrii](
	[IDDepartParam] [int] IDENTITY(1,1) NOT NULL,
	[IDDepartament] [int] NOT NULL,
	[Luna] [smallint] NOT NULL,
	[An] [smallint] NOT NULL DEFAULT ((0)),
	[NrPaturi] [int] NOT NULL DEFAULT ((0)),
	[Suprafata] [decimal](18, 2) NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT (0),
 CONSTRAINT [PK_DepartamenteParametrii] PRIMARY KEY CLUSTERED ([IDDepartParam] ASC),
 CONSTRAINT [FK_DepartamenteParametrii_Departamente] FOREIGN KEY([IDDepartament]) REFERENCES [dbo].[Departamente] ([IDDepartament]),
 CONSTRAINT [FK_DepartamenteParametrii_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
) ON [PRIMARY]
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GenDepartamenteParam]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GenDepartamenteParam]
GO


CREATE PROCEDURE [dbo].[GenDepartamenteParam]
	@CodUnitate smallint,
	@An smallint,
	@Luna smallint
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @cate int
	DECLARE @perioada int
	SELECT @cate = COUNT(*) FROM [DepartamenteParametrii] WHERE CodUnitate=@CodUnitate AND Luna=@Luna and An=@An
	IF @cate<>0
		return
	SELECT TOP 1 @cate = COUNT(*),@perioada = An*100+Luna FROM [DepartamenteParametrii] WHERE CodUnitate=@CodUnitate
	AND An*100+Luna<=@An*100+@Luna
	GROUP BY An*100+Luna
	ORDER BY An*100+Luna
	IF @cate<>0
	BEGIN
		INSERT INTO [DepartamenteParametrii]
			   ([IDDepartament]
			   ,[Luna]
			   ,[An]
			   ,[NrPaturi]
			   ,[Suprafata]
			   ,[CodUnitate])
		 SELECT IDDepartament,@Luna, @An,NrPaturi,Suprafata,@CodUnitate  FROM [DepartamenteParametrii] WHERE CodUnitate=@CodUnitate
			AND An*100+Luna=@perioada
	END
	ELSE
		BEGIN
			INSERT INTO [DepartamenteParametrii]
				   ([IDDepartament]
				   ,[Luna]
				   ,[An]
				   ,[NrPaturi]
				   ,[Suprafata]
				   ,[CodUnitate])
			 SELECT IDDepartament,@Luna, @An,CAST(COALESCE([NumericField],0) as int) NrParuri,COALESCE(Suprafata,0) Suprafata,@CodUnitate  FROM [Departamente] WHERE CodUnitate=@CodUnitate
		END
	
		
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exbug_UpdateDepartamenteParametrii]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_UpdateDepartamenteParametrii]
GO


CREATE PROCEDURE [dbo].[exbug_UpdateDepartamenteParametrii]
	@codUnitate smallint, 
	@An smallint,
	@Luna smallint
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE [Departamente] SET
	NumericField = a.NrPaturi,
	Suprafata = a.Suprafata
	FROM DepartamenteParametrii a
	WHERE a.IDDepartament = [Departamente].IDDepartament AND a.CodUnitate = @codUnitate
	AND An = @an aND Luna = @luna

	
END

GO





IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CreazaArhiva]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CreazaArhiva]
GO


CREATE PROCEDURE [dbo].[CreazaArhiva]
	@An smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @strContAn varchar(40)
	DECLARE @Comanda varchar(3000)
	DECLARE @existTableCont bit
	
	SET @strContAn = 'arhConturi'
	SET @existTableCont = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strContAn)
		SET @existTableCont = 1
	
	--verifica existenta tabeleli arhiva
	SET @Comanda = 'CREATE TABLE [' + @strContAn + '] (
		[Cont] [dbo].[TipCont] NOT NULL DEFAULT (''''),
		[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
		[Denumire] [varchar](200) NOT NULL DEFAULT (''''),
		[Tip] [char](1) NOT NULL DEFAULT (''A''),
		[Terminal] [bit] NOT NULL DEFAULT ((1)),
		[ContParinte] [dbo].[TipCont] NOT NULL DEFAULT (''''),
		[EsteAnalitic] [bit] NOT NULL DEFAULT ((1)),
		[DebitInitial] [decimal](20, 2) NOT NULL DEFAULT (0),
		[CreditInitial] [decimal](20, 2) NOT NULL DEFAULT (0),
		[DebitPrecedent] [decimal](20, 2) NOT NULL DEFAULT (0),
		[CreditPrecedent] [decimal](20, 2) NOT NULL DEFAULT (0),
		[Categorie] [tinyint] NOT NULL DEFAULT (0),
		[RulajDebit] [decimal](20, 2) NOT NULL DEFAULT (0),
		[RulajCredit] [decimal](20, 2) NOT NULL DEFAULT (0),
		[Sold] [decimal](20, 2) NOT NULL DEFAULT ((0)),
		[SortValue] [dbo].[TipCont] NOT NULL DEFAULT (''''),
		[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strContAn +'] PRIMARY KEY CLUSTERED([Cont] ASC,[CodUnitate] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strContAn +'_' + @strContAn + '] FOREIGN KEY([ContParinte], [CodUnitate],[An]) REFERENCES ['+@strContAn+'] ([Cont], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strContAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate]),
	CONSTRAINT [CK_'+@strContAn+'_Tip] CHECK  (([Tip]=''A'' OR [Tip]=''P'' OR [Tip]=''B''))
	)'
	
	IF @existTableCont = 0
		EXEC(@Comanda)


	DECLARE @strAAAn varchar(40)
	DECLARE @existTableAA bit
	
	SET @strAAAn = 'arhArticoleAliniate' 
	SET @existTableAA = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strAAAn)
		SET @existTableAA = 1

	SET @Comanda = 'CREATE TABLE ['+@strAAAn+'](
	[ArtAl] [varchar](40) NOT NULL,
	[Denumire] [varchar](200) NOT NULL,
	[AAParinte] [varchar](40) NOT NULL,
	[Terminal] [bit] NOT NULL,
	[Titlu] [varchar](100) NOT NULL,
	[Categorii] [varchar](50) NOT NULL,
	[TermDenumire] [varchar](100) NOT NULL,
	[StartDenumire] [varchar](100) NOT NULL,
	[Anexa6] [bit] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strAAAn+'] PRIMARY KEY CLUSTERED([ArtAl] ASC,[CodUnitate] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strAAAn+'_'+@strAAAn+'] FOREIGN KEY([AAParinte], [CodUnitate],[An]) REFERENCES ['+@strAAAn+'] ([ArtAl], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strAAAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
	)'
	IF @existTableAA = 0
		EXEC(@Comanda)

	---Bilant----------------------------
	DECLARE @strBilantAn varchar(40)
	DECLARE @existTableBilant bit
	
	SET @strBilantAn = 'arhBilant'
	SET @existTableBilant = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strBilantAn)
		SET @existTableBilant = 1

	SET @Comanda = 'CREATE TABLE ['+@strBilantAn+'](
	[row] [int] NOT NULL,
	[nr_rand] [text] NOT NULL,
	[text] [text] NOT NULL,
	[codfix] [int] NOT NULL,
	[cod] [varchar](10) NOT NULL,
	[coltypei] [char](1) NOT NULL,
	[soldi] [text] NOT NULL,
	[coltypef] [char](1) NOT NULL,
	[soldf] [text] NOT NULL,
	[eval] [int] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[Boldat] [bit] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strBilantAn+'] PRIMARY KEY CLUSTERED([row] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strBilantAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
	)'
	IF @existTableBilant = 0
		EXEC(@Comanda)
	---Conturi Valuta--------------------
	
	DECLARE @strContValutaAn varchar(40)
	DECLARE @existTableContValuta bit
	
	SET @strContValutaAn = 'arhConturiValuta'
	SET @existTableContValuta = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strContValutaAn)
		SET @existTableContValuta = 1

	SET @Comanda = 'CREATE TABLE ['+@strContValutaAn+'](
	[CodValuta] [smallint] NOT NULL DEFAULT ((0)),
	[Cont] [dbo].[TipCont] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[DebitInitial] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[CreditInitial] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[DebitPrecedent] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[CreditPrecedent] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[IdJurnal] [int] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strContValutaAn+'] PRIMARY KEY CLUSTERED([CodValuta] ASC,[Cont] ASC,[CodUnitate] ASC,[IdJurnal] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strContValutaAn+'_'+@strContAn+'] FOREIGN KEY([Cont], [CodUnitate],[An]) REFERENCES ['+@strContAn+'] ([Cont], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strContValutaAn+'_Jurnale] FOREIGN KEY([IdJurnal]) REFERENCES [dbo].[Jurnale] ([IDJurnal]),
	CONSTRAINT [FK_'+@strContValutaAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate]),
	CONSTRAINT [FK_'+@strContValutaAn+'_Valuta] FOREIGN KEY([CodValuta]) REFERENCES [dbo].[Valuta] ([CodValuta])
	)'

	IF @existTableContValuta = 0
		EXEC(@Comanda)

	---Jurnale Solduri Initiale--------------------
	
	DECLARE @strJurnaleSIAn varchar(40)
	DECLARE @existTableJurnaleSI bit
	
	SET @strJurnaleSIAn = 'arhJurnaleSolduriInitiale'
	SET @existTableJurnaleSI = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strJurnaleSIAn)
		SET @existTableJurnaleSI = 1

	SET @Comanda = 'CREATE TABLE ['+@strJurnaleSIAn+'](
	[IDJurnal] [int] NOT NULL,
	[Cont] [dbo].[TipCont] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[DebitInitial] [decimal](18, 2) NOT NULL DEFAULT ((0)),
	[CreditInital] [decimal](18, 2) NOT NULL DEFAULT ((0)),
	[DebitPrecedent] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[CreditPrecedent] [decimal](20, 2) NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strJurnaleSIAn+'] PRIMARY KEY CLUSTERED ([IDJurnal] ASC,[Cont] ASC,[CodUnitate] ASC,[An] ASC),
	CONSTRAINT [FK_'+@strJurnaleSIAn+'_' + @strContAn + '] FOREIGN KEY([Cont], [CodUnitate],[An]) REFERENCES [' + @strContAn + '] ([Cont], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strJurnaleSIAn+'_Jurnale] FOREIGN KEY([IDJurnal]) REFERENCES [Jurnale] ([IDJurnal]),
	CONSTRAINT [FK_'+@strJurnaleSIAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [Unitati] ([CodUnitate])
	)'
	
	IF @existTableJurnaleSI = 0
		EXEC(@Comanda)


	---Ordonantari--------------------
	
	DECLARE @strOrdonantariAn varchar(40)
	DECLARE @existTableOrdonantari bit
	
	SET @strOrdonantariAn = 'arhOrdonantari'
	SET @existTableOrdonantari = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strOrdonantariAn)
		SET @existTableOrdonantari = 1

	SET @Comanda = 'CREATE TABLE ['+@strOrdonantariAn+'](
	[NrDoc] [decimal](18, 0) NOT NULL,
	[DataDoc] [datetime] NOT NULL,
	[IDFurnizor] [bigint] NOT NULL,
	[ValoareLei] [decimal](20, 2) NOT NULL,
	[IDJurnal] [int] NOT NULL,
	[Scop] [varchar](100) NOT NULL,
	[DocJust] [varchar](200) NOT NULL,
	[AngLegal] [varchar](50) NOT NULL,
	[Achitat] [bit] NOT NULL,
	[IdOrdonantare] [int] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT 0,
	[IdModPlata] [int] NOT NULL DEFAULT 1,
	[TotalDatorat] [decimal](18, 2) NOT NULL DEFAULT 0,
	[TotalAvansuri] [decimal](18, 2) NOT NULL DEFAULT 0,
	[ArtAl] [varchar](40) NOT NULL DEFAULT '''',
	[VizatCFP] [bit] NOT NULL,
	[MotivRefuz] [varchar](250) NOT NULL DEFAULT '''',
	[IDResponsabil] [int] NOT NULL DEFAULT 0,
	[DataVizaCFP] [datetime] NOT NULL DEFAULT (getdate()),
	[NrRegCFP] [varchar](20) NOT NULL DEFAULT (''FARA''),
	[IBANSursa] [varchar](24) NULL,
	[IBANDest] [varchar](24) NULL,
	[IDDocumentPlata] [int] NULL,
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strOrdonantariAn+'] PRIMARY KEY CLUSTERED ([IdOrdonantare] ASC),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_'+@strAAAn+'] FOREIGN KEY([ArtAl], [CodUnitate],[An]) REFERENCES ['+@strAAAn+'] ([ArtAl], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_Parteneri] FOREIGN KEY([IDfurnizor]) REFERENCES [dbo].[Parteneri] ([IdPartener]),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_Jurnale] FOREIGN KEY([IDJurnal]) REFERENCES [dbo].[Jurnale] ([IDJurnal]),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_ModPlata] FOREIGN KEY([IdModPlata]) REFERENCES [dbo].[ModPlata] ([IDModPlata]),
	CONSTRAINT [FK_'+@strOrdonantariAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])	
	)'
	IF @existTableOrdonantari = 0
		EXEC(@Comanda)

	
	---RezultatPatrimonial--------------------
	
	DECLARE @strRezultatPatrimonialAn varchar(40)
	DECLARE @existTableRezultatPatrimonial bit
	
	SET @strRezultatPatrimonialAn = 'arhRezultatPatrimonial'
	SET @existTableRezultatPatrimonial = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strRezultatPatrimonialAn)
		SET @existTableRezultatPatrimonial = 1

	SET @Comanda = 'CREATE TABLE ['+@strRezultatPatrimonialAn+'](
	[row] [int] NOT NULL,
	[nr_rand] [text] NOT NULL,
	[text] [text] NOT NULL,
	[codfix] [int] NOT NULL,
	[cod] [varchar](10) NOT NULL,
	[coltypei] [char](1) NOT NULL,
	[soldi] [text] NOT NULL,
	[coltypef] [char](1) NOT NULL,
	[soldf] [text] NOT NULL,
	[eval] [int] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[Boldat] [bit] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strRezultatPatrimonialAn+'] PRIMARY KEY CLUSTERED ([row] ASC),
	CONSTRAINT [FK_'+@strRezultatPatrimonialAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
	)'
	IF @existTableRezultatPatrimonial = 0
		EXEC(@Comanda)

	---RulajeDoc--------------------
	
	DECLARE @strRulajeDocAn varchar(40)
	DECLARE @existTableRulajeDoc bit
	
	SET @strRulajeDocAn = 'arhRulajeDoc'
	SET @existTableRulajeDoc = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strRulajeDocAn)
		SET @existTableRulajeDoc = 1

	SET @Comanda = 'CREATE TABLE ['+@strRulajeDocAn+'](
	[IDDocument] [bigint] NOT NULL,
	[ValoareLei] [decimal](20, 2) NOT NULL DEFAULT (0),
	[ValoareValuta] [decimal](20, 2) NOT NULL DEFAULT (0),
	[NumarDoc] [decimal](18, 0) NOT NULL DEFAULT (0),
	[DataDoc] [datetime] NOT NULL DEFAULT (getdate()),
	[NumarNota] [varchar](50) NOT NULL DEFAULT (''''),
	[TipFormula] [smallint] NOT NULL DEFAULT (0),
	[IDJurnal] [int] NULL DEFAULT (0),
	[Observatii] [varchar](200) NULL,
	[xUser] [varchar](50) NOT NULL  DEFAULT (suser_sname()),
	[xHost] [varchar](50) NOT NULL DEFAULT (host_name()),
	[xDate] [datetime] NOT NULL  DEFAULT (0),
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strRulajeDocAn+'] PRIMARY KEY CLUSTERED ([IDDocument] ASC),
	CONSTRAINT [FK_'+@strRulajeDocAn+'_Jurnale] FOREIGN KEY([IDJurnal]) REFERENCES [Jurnale] ([IDJurnal]),
	CONSTRAINT [FK_'+@strRulajeDocAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [Unitati] ([CodUnitate]),
	CONSTRAINT [CK_'+@strRulajeDocAn+'_Tip_FORMULA] CHECK  (([TipFormula] = 3 or ([TipFormula] = 2 or ([TipFormula] = 1 or [TipFormula] = 0))))
	)'
	IF @existTableRulajeDoc = 0
		EXEC(@Comanda)

	---Rulaje--------------------
	
	DECLARE @strRulajeAn varchar(40)
	DECLARE @existTableRulaje bit
	
	SET @strRulajeAn = 'arhRulaje'
	SET @existTableRulaje = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strRulajeAn)
		SET @existTableRulaje = 1

	SET @Comanda = 'CREATE TABLE ['+@strRulajeAn+'](
	[CodFormula] [bigint] NOT NULL,
	[ContDebit] [dbo].[TipCont] NOT NULL,
	[ContCredit] [dbo].[TipCont] NOT NULL,
	[CodValuta] [smallint] NOT NULL DEFAULT (0),
	[IDDocument] [bigint] NOT NULL,
	[ValoareValuta] [decimal](20, 2) NOT NULL DEFAULT (0),
	[Curs] [decimal](18, 4) NOT NULL DEFAULT (1),
	[ValoareLei] [decimal](20, 2) NOT NULL DEFAULT (0),
	[DataDocJust] [datetime] NOT NULL,
	[TipDocJust] [varchar](10) NOT NULL  DEFAULT (''NC''),
	[NumarDocJust] [decimal](18, 0) NOT NULL,
	[ExplicatiiDocJust] [varchar](200) NOT NULL,
	[NrCrt] [smallint] NOT NULL DEFAULT (1),
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strRulajeAn+'] PRIMARY KEY CLUSTERED ([CodFormula] ASC),
	CONSTRAINT [FK_'+@strRulajeAn+'_'+@strContAn+'_Credit] FOREIGN KEY([ContCredit], [CodUnitate],[An]) REFERENCES ['+@strContAn+'] ([Cont], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strRulajeAn+'_'+@strContAn+'_Debit] FOREIGN KEY([ContDebit], [CodUnitate],[An]) REFERENCES ['+@strContAn+'] ([Cont], [CodUnitate],[An]),
	CONSTRAINT [FK_'+@strRulajeAn+'_'+@strRulajeDocAn+'] FOREIGN KEY([IDDocument]) REFERENCES ['+@strRulajeDocAn+'] ([IDDocument]),
	CONSTRAINT [FK_'+@strRulajeAn+'_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate]),
	CONSTRAINT [FK_'+@strRulajeAn+'_Valuta] FOREIGN KEY([CodValuta]) REFERENCES [dbo].[Valuta] ([CodValuta])
	)'

	IF @existTableRulaje = 0
		EXEC(@Comanda)
	

	---VenituriSiCheltuieli--------------------
	
	DECLARE @strVenituriSiCheltuieliAn varchar(40)
	DECLARE @existTableVenituriSiCheltuieli bit
	
	SET @strVenituriSiCheltuieliAn = 'arhVenituriSiCheltuieli'
	SET @existTableVenituriSiCheltuieli = 0
	IF EXISTS(SELECT * FROM sys.tables WHERE NAME LIKE @strVenituriSiCheltuieliAn)
		SET @existTableVenituriSiCheltuieli = 1

	SET @Comanda = 'CREATE TABLE ['+@strVenituriSiCheltuieliAn+'](
	[row] [int] NOT NULL,
	[nr_rand] [text] NOT NULL,
	[text] [text] NOT NULL,
	[codfix] [int] NOT NULL,
	[cod] [varchar](20) NOT NULL,
	[coltypei] [char](1) NOT NULL,
	[soldi] [text] NOT NULL,
	[coltypef] [char](1) NOT NULL,
	[soldf] [text] NOT NULL,
	[eval] [int] NOT NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[Boldat] [bit] NOT NULL DEFAULT ((0)),
	[An] [smallint] NOT NULL DEFAULT (0),
	CONSTRAINT [PK_'+@strVenituriSiCheltuieliAn+'] PRIMARY KEY CLUSTERED ([row] ASC)
	)'
	IF @existTableVenituriSiCheltuieli = 0
		EXEC(@Comanda)



END

GO

EXEC [CreazaArhiva] ''
GO




IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[arhNumarNotaRulajeDocView]'))
DROP VIEW [dbo].[arhNumarNotaRulajeDocView]
GO


CREATE VIEW [dbo].[arhNumarNotaRulajeDocView]
AS
SELECT DISTINCT NumarNota, CodUnitate,An
FROM         dbo.arhRulajeDoc

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetBalanta]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetBalanta]
GO

CREATE PROCEDURE [dbo].[GetBalanta] 
	-- Add the parameters for the stored procedure here
	@pDtDataStartUI datetime,
	@pDtDataEndUI datetime,
	@pJurnal int,
	@pCategorie int,
	@pStrContStart TipCont,
	@pStrContEnd TipCont,
	@pCfm bit,
	@pAnalitica bit,
	@pCfmRulaje bit,
	@pTerminal bit,
	@An smallint,
	@Tgc bit
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
DECLARE @CodUnitate smallint
DECLARE @cmd varchar(5000)
DECLARE @strCodUnitate varchar(20)
DECLARE @DtDataStartUI varchar(20)
DECLARE @DtDataEndUI varchar(20)
DECLARE @strCategorie varchar(100)
DECLARE @strAn varchar(10)

SET @CodUnitate=0
SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()
SET @strCodUnitate = cast(@CodUnitate AS varchar(20))

DECLARE @ConturiTGC AS varchar(2000)
SET @ConturiTGC = ','
SELECT @ConturiTGC=@ConturiTGC + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{TGC}%' AND [CodUnitate]=@CodUnitate

DECLARE @ConturiSS AS varchar(2000)
SET @ConturiSS = ','
SELECT @ConturiSS=@ConturiSS + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CSS}%' AND [CodUnitate]=@CodUnitate



SET @DtDataStartUI = cast(YEAR(@pDtDataStartUI) as varchar(4)) + '-' +cast(MONTH(@pDtDataStartUI) as varchar(2)) + '-' + cast(DAY(@pDtDataStartUI) as varchar(2))
SET @DtDataEndUI = cast(YEAR(@pDtDataEndUI) as varchar(4)) + '-' +cast(MONTH(@pDtDataEndUI) as varchar(2)) + '-' + cast(DAY(@pDtDataEndUI) as varchar(2))

SET @strCategorie = LTRIM(RTRIM(cast(@pCategorie as varchar(10))))
SET @strAn = cast(@An as varchar(10))

IF @Tgc=1
	SET @strCategorie = @strCategorie + ',3'

SET @cmd = 'SELECT  Cont,ContParinte,Terminal,Denumire,Tip,Categorie,SortValue,DebInit [SoldInitDeb],CreInit [SoldInitCre], 
    DebPrecCalc+DebitPrec [RulPrecDeb], CrePrecCalc+CreditPrec [RulPrecCre], 
    DebCurent [RulCurentDeb], CreCurent [RulCurentCre],
	DebInit + DebPrecCalc+DebitPrec+ COALESCE(DebCurent, 0) RulTotalDeb, CreInit +CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0) RulTotalCre, 
	dbo.SoldDebit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalDeb, 
	dbo.SoldCredit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalCre
	FROM
	(SELECT c.Cont, C.Denumire,C.EsteAnalitic, C.Tip,C.Categorie,C.ContParinte,C.Terminal,C.SortValue, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit],
	sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc, c.DebitPrecedent [DebitPrec], c.CreditPrecedent [CreditPrec], 
	sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
	FROM '
IF @Tgc=1
BEGIN
	DECLARE @cmdCont varchar(2000)
	SET @cmdCont =  
	'(SELECT a.[Cont],a.[CodUnitate],a.[Denumire],a.[Tip],a.[Terminal],a.[ContParinte],a.[EsteAnalitic],a.[Categorie],a.[SortValue],sum(a.[DebitPrecedent]) DebitPrecedent ,sum(a.[CreditPrecedent]) CreditPrecedent,
	sum(b.DebitInitial) [DebitInitial] ,sum(b.creditInitial) [CreditInitial]
	FROM [Conturi] a JOIN [Conturi] b
	ON dbo.IsContInclus(a.cont,b.cont,a.Terminal)=1 and a.codunitate=b.codunitate
	where a.[CodUnitate]=' + @strCodUnitate +' and a.categorie=3 and b.ContParinte=''N/A''
	group by a.[Cont],a.[CodUnitate],a.[Denumire],a.[Tip],a.[Terminal],a.[ContParinte],a.[EsteAnalitic],a.[Categorie],a.[SortValue]
	UNION
	SELECT  a2.[Cont],a2.[CodUnitate],a2.[Denumire],a2.[Tip],a2.[Terminal],a2.[ContParinte],a2.[EsteAnalitic],a2.[Categorie],a2.[DebitPrecedent],a2.[CreditPrecedent],
	a2.[DebitInitial] ,a2.[CreditInitial] FROM [Conturi] a2
	WHERE a2.codunitate='+@strCodUnitate +' and a2.categorie=0 )'
	IF @An <> 0 
		SET @cmdCont = REPLACE(@cmdCont,'[Conturi]', '[arhConturi]')
	
	SET @cmd = @cmd  + @cmdCont
END
ELSE
BEGIN
	IF @An <> 0 
		SET @cmd = @cmd  + '[arhConturi]'
	ELSE
		SET @cmd = @cmd  + '[Conturi]'
END

SET @cmd = @cmd  + ' c
    LEFT OUTER JOIN 
	(SELECT sum(sumaDebitPrec) DebPrecCalc, sum(sumaCreditPrec) AS CrePrecCalc,
	   sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal FROM
			(SELECT 			    
				coalesce(SUM(CASE WHEN D.DataDoc<''' + @DtDataStartUI + ''' THEN F.ValoareLei ELSE 0 END),0) sumaDebitPrec,
				coalesce(SUM(CASE WHEN D.DataDoc>=''' + @DtDataStartUI +''' AND D.DataDoc<=''' + @DtDataEndUI +''' THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
				0.00 AS sumaCreditPrec, 0.00 AS sumaCredit, 
				F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal
				FROM '
--[RulajeDoc]
IF @An <> 0 
	SET @cmd = @cmd  + '[arhRulajeDoc]'
ELSE
	SET @cmd = @cmd  + '[RulajeDoc]'

SET @cmd = @cmd  + ' D INNER JOIN '
--[Rulaje]
IF @An <> 0 
	SET @cmd = @cmd  + '[arhRulaje]'
ELSE
	SET @cmd = @cmd  + '[Rulaje]'


SET @cmd = @cmd  + ' F ON D.[IDDocument] = F.[IDDocument] 
				WHERE  d.[CodUnitate] =' +  @strCodUnitate
			
IF 	@pJurnal<>-1	
	SET @cmd = @cmd + 	' AND D.IDJurnal= ' + cast(@pJurnal as varchar(10)) 
					
SET @cmd = @cmd  + ' GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal
			UNION
			SELECT 
				0.00 AS sumaDebitPrec, 0.00 AS sumaDebit,
				coalesce(SUM(CASE WHEN D.DataDoc < ''' + @DtDataStartUI+''' THEN F.ValoareLei ELSE 0 END),0) sumaCreditPrec,
				coalesce(SUM(CASE WHEN D.DataDoc >=''' + @DtDataStartUI+''' AND D.DataDoc<=''' + @DtDataEndUI +''' THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
                F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal
				FROM '
--[RulajeDoc]
IF @An <> 0 
	SET @cmd = @cmd  + '[arhRulajeDoc]'
ELSE
	SET @cmd = @cmd  + '[RulajeDoc]'

SET @cmd = @cmd  + ' D INNER JOIN '
--[Rulaje]
IF @An <> 0 
	SET @cmd = @cmd  + '[arhRulaje]'
ELSE
	SET @cmd = @cmd  + '[Rulaje]'

SET @cmd = @cmd  + ' F ON D.[IDDocument] = F.[IDDocument] 
				WHERE d.[CodUnitate] = '+@strCodUnitate 
IF 	@pJurnal<>-1	
	SET @cmd = @cmd + 	' AND D.IDJurnal= ' + cast(@pJurnal as varchar(10)) 
			

SET @cmd = @cmd +  ' GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal) t1
	GROUP BY Cont,CodUnitate,IDJurnal
	) RUL 
	ON dbo.IsContInclus(c.cont,rul.cont,c.Terminal)=1 AND c.CodUnitate=rul.CodUnitate
	WHERE  c.[Cont]<>''N/A'' AND Categorie in ('+ @strCategorie + ') AND c.[CodUnitate] ='+ @strCodUnitate + '
	AND c.Cont >= '''+@pStrContStart + ''''
IF @pStrContEnd	<> ''
	SET @cmd = @cmd + ' AND c.Cont <='''+ @pStrContEnd +''''
	
SET @cmd = @cmd +  'GROUP BY  c.Cont, C.Denumire,  C.Tip, C.Categorie, C.ContParinte,C.Terminal,C.SortValue,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent,C.EsteAnalitic
	) [balanta] '

DECLARE @cmdFilt varchar(8000)
SET @cmdfilt = ' WHERE ' 
IF @pCfm <> 1
BEGIN
	IF @An <> 0 
		SET @cmdfilt  = @cmdfilt +  ' (EXISTS (SELECT ContDebit, ContCredit FROM arhRulaje rj JOIN  arhRulajeDoc rjd ON rj.IDDocument=rjd.IDDocument  WHERE ((ContDebit LIKE Cont + ''%'') OR (ContCredit LIKE Cont + ''%'')) AND rj.[CodUnitate]='+@strCodUnitate+' AND ('+cast(@pJurnal as varchar(10))+'=-1 OR rjd.IDJurnal = '+cast(@pJurnal as varchar(10))+')) OR ((DebInit<>0) OR (CreInit<>0))) '
	ELSE
		SET @cmdfilt  = @cmdfilt +  ' (EXISTS (SELECT ContDebit, ContCredit FROM Rulaje rj  JOIN  RulajeDoc rjd ON rj.IDDocument=rjd.IDDocument WHERE ((ContDebit LIKE Cont + ''%'') OR (ContCredit LIKE Cont + ''%'')) AND rj.[CodUnitate]='+@strCodUnitate+' AND ('+cast(@pJurnal as varchar(10))+'=-1 OR rjd.IDJurnal = '+cast(@pJurnal as varchar(10))+')) OR (('+cast(@pJurnal as varchar(10))+'=-1 AND ((DebInit<>0) OR (CreInit<>0))) ) ) '
END

IF @pAnalitica <> 1
BEGIN
	IF @pCfm <> 1
		SET @cmdfilt  = @cmdfilt +  ' AND '
	IF @Tgc=0
		SET @cmdfilt  = @cmdfilt +  ' (([EsteAnalitic]=0)  OR (dbo.IsContIn('',''+ Cont+'','','''+@ConturiSS+''')<>0)) '
	ELSE
		SET @cmdfilt  = @cmdfilt +  ' (([EsteAnalitic]=0) OR (dbo.IsContIn('',''+ Cont+'','','''+@ConturiTGC+''')<>0)) '

END
IF @pCfmRulaje <> 1
BEGIN
	IF (@pCfm <> 1) OR (@pAnalitica <> 1)
		SET @cmdfilt  = @cmdfilt +  ' AND '
	SET @cmdfilt  = @cmdfilt +  ' (DebCurent<>0 OR CreCurent<>0) '
END

IF @pTerminal <> 0
BEGIN
	IF (@pCfm <> 1) OR (@pAnalitica <> 1) OR (@pCfmRulaje <> 1)
		SET @cmdfilt  = @cmdfilt +  ' AND '
	SET @cmdfilt  = @cmdfilt +  '([Terminal]=1)'
END
IF @cmdfilt <> ' WHERE '
	SET @cmd  = @cmd +  @cmdFilt
SET @cmd  = @cmd +  ' ORDER BY Cont '
PRINT(@cmdFilt)
PRINT(@cmd)
EXEC(@cmd) 
IF @@ERROR != 0 GOTO EROARE  GOTO Gata
EROARE:
	ROLLBACK
	RAISERROR ('Eroare balanta!',16,-1)  GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	GOTO Gata
Gata:

END


GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaJurnalCumparariTVALaIncasare]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaJurnalCumparariTVALaIncasare]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaJurnalCumparariTVALaIncasare] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal, 
	NrDataDocPlata,
	CASE WHEN doc.IDRegimFiscal IN (4,17,18,19,27) THEN 
		CASE WHEN doc.IDTipDocument=10 THEN ValoareTotalaSiTVA ELSE Coalesce(ValPlatCuTVA,0) END  
	ELSE  0  END ValPlatCuTVA, 
	CASE WHEN doc.IDRegimFiscal IN (4,17,18,19,27) THEN 
	CASE WHEN doc.IDTipDocument=10 THEN Baza ELSE Coalesce(BazaExigibila,0) END 
	ELSE  0  END BazaExigibila,
	CASE WHEN doc.IDRegimFiscal IN (4,17,18,19,27) THEN 
	CASE WHEN doc.IDTipDocument=10 THEN TVA ELSE Coalesce(TVAExigibil,0) END 
	ELSE  0  END TVAExigibil,
	CASE WHEN doc.IDRegimFiscal IN (4,17,18,19,27) THEN 
	CASE WHEN doc.IDTipDocument=10 THEN 0 ELSE doc.TotalValoare - Coalesce(BazaExigibila,0) END 
	ELSE  0  END BazaNeExigibila,
	CASE WHEN doc.IDRegimFiscal IN (4,17,18,19,27) THEN 
	CASE WHEN doc.IDTipDocument=10 THEN 0 ELSE TotalValoareTVA  - Coalesce(TVAExigibil,0) END 
	ELSE  0  END TVANeExigibila,
	docd.*
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
		(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND (dd.CotaTVA=24 OR dd.CotaTVA=19)  THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND (dd.CotaTVA=24 OR dd.CotaTVA=19) THEN ValoareTVA ELSE 0 END) TVA24,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) THEN ValoareTVA ELSE 0 END) TVA,		
		SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) InversBaza,
		SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN ValoareTVA ELSE 0 END) InversTVA,
		SUM(CASE WHEN dd.IDRegimFiscal IN (52) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) ImportTaraScutite,
		SUM(CASE WHEN dd.IDRegimFiscal IN (57) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) ObligatBaza,
		SUM(CASE WHEN dd.IDRegimFiscal IN (57) THEN ValoareTVA ELSE 0 END) ObligatTVA,				
		SUM(CASE WHEN dd.IDRegimFiscal IN (51) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) ImportBunuriBaza,
		SUM(CASE WHEN dd.IDRegimFiscal IN (51) THEN ValoareTVA ELSE 0 END) ImportBunuriTVA,		
		SUM(CASE WHEN dd.IDRegimFiscal IN (55) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) BCScutit,		
		SUM(CASE WHEN dd.IDRegimFiscal IN (56) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) BCNeimp,
		--SUM(CASE WHEN dd.IDRegimFiscal IN (51) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) BCBI,
		SUM(CASE WHEN dd.IDRegimFiscal IN (53) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) DRBI,
		SUM(CASE WHEN dd.IDRegimFiscal IN (54) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) NFBI,
		--SUM(CASE WHEN dd.IDRegimFiscal IN (51) THEN ValoareTVA ELSE 0 END) BCTVA,
		SUM(CASE WHEN dd.IDRegimFiscal IN (53) THEN ValoareTVA ELSE 0 END) DRTVA,
		SUM(CASE WHEN dd.IDRegimFiscal IN (54) THEN ValoareTVA ELSE 0 END) NFTVA
		
		FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
		GROUP BY dd.IDDocument) docd 
	ON doc.IDDocument = docd.IDDocument LEFT OUTER JOIN
	(SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
	 SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
	 FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data>=@DeLaData AND dp1.Data<=@PanaLaData
		GROUP BY ddp1.IDDocument) selPlati ON selPlati.IDDocument = docd.IDDocument	
	WHERE 
	tipd.IDTipDocument in (0,10) AND doc.CodUnitate=@CodUnitate AND 
	--LTRIM(RTRIM(part.AtributFiscal))<>'' AND
    (doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData)
    
   
    UNION
    --toate cele vechi neplatite
    SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal, 
	selPlati.NrDataDocPlata,
	Coalesce(selPlati.ValPlatCuTVA,0) ValPlatCuTVA, 
	Coalesce(selPlati.BazaExigibila,0) BazaExigibila,
	Coalesce(selPlati.TVAExigibil,0) TVAExigibil,
	doc.TotalValoare - COALESCE(selPlatiAnterior.BazaExigibila,0) -  Coalesce(selPlati.BazaExigibila,0) BazaNeExigibila,
	TotalValoareTVA  - COALESCE(selPlatiAnterior.TVAExigibil,0) - Coalesce(selPlati.TVAExigibil,0) TVANeExigibila,
	docd.*
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
		(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND (dd.CotaTVA=24 OR dd.CotaTVA=19)  THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND (dd.CotaTVA=24 OR dd.CotaTVA=19) THEN ValoareTVA ELSE 0 END) TVA24,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) THEN ValoareTVA ELSE 0 END) TVA,				
		0 InversBaza,0 InversTVA,0 ImportTaraScutite,0 ObligatBaza,0 ObligatTVA,0 ImportBunuriBaza,0 ImportBunuriTVA,0 BCScutit,
		0 BCNeimp,
		--0 BCBI,
		0 DRBI,0 NFBI,
		--0 BCTVA,
		0 DRTVA,0 NFTVA
		FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
		GROUP BY dd.IDDocument) docd 
	ON doc.IDDocument = docd.IDDocument LEFT OUTER JOIN 
	(
		SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<=@PanaLaData AND dp1.Data>=@DeLaData
		GROUP BY ddp1.IDDocument
	) selPlati ON selPlati.IDDocument = docd.IDDocument LEFT OUTER JOIN 	
	(
		SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<@DeLaData 
		GROUP BY ddp1.IDDocument
	) selPlatiAnterior ON selPlatiAnterior.IDDocument = docd.IDDocument
	WHERE 
	  YEAR(doc.DataDoc) > 2012 AND                     -- dupa 2012 
	  tipd.IDTipDocument in (0) AND                 -- pentru facturile de cumparare fara bonuri fiscale
	  doc.CodUnitate=@CodUnitate AND                -- pentru unitatea curenta
	  --LTRIM(RTRIM(part.AtributFiscal))<>'' AND      -- pentru partenerii care au atribut fiscal
	  doc.DataDoc < @DeLaData AND                   -- pentru facturile de dinaintea intervalului selectat
	  --doc.DataDoc >= DATEADD(day,-90,@DeLaData) AND -- dar nu mai vechi de 90 de zile
	  doc.IDRegimFiscal IN (4,17,18,19,27) AND      -- cu regim fiscal cu TVA interne
	  --ValoareTotalaSiTVA >= COALESCE(ValPlatCuTVA,0) AND
      EXISTS(SELECT * FROM DocumenteDetaliu dd WHERE dd.IDDocument = docd.IDDocument AND dd.TVAIncasare=1) AND 
                                                    -- pentru facturile care au o linie cu tVA la incasare
      ABS(doc.Total) >  ABS(COALESCE((SELECT SUM(dpd.Valoare) FROM DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata = dp.IDDocumentPlata
	  WHERE dpd.IDDocument = docd.IDDocument AND dp.Data < @DeLaData),0))
												    -- care au ramas neachitate pana in perioada selectata 
    
	ORDER BY DataDoc
		
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaJurnalVerticalTVALaIncasare]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaJurnalVerticalTVALaIncasare]
GO

CREATE PROCEDURE [dbo].[fares_GenereazaJurnalVerticalTVALaIncasare] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@cumparari smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
	docd.*,rf.Denumire AS DenRegim,
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN ABS(COALESCE(ValPlatCuTVA,0))> ABS(ValoareTotalaSiTVA) THEN ValoareTotalaSiTVA ELSE COALESCE(ValPlatCuTVA,0) END 
	ELSE 0 END 
	ELSE ValPlatCuTVA END ValPlatCuTVA,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
	  CASE WHEN ABS(COALESCE(BazaExigibila,0)) > ABS(Baza) THEN Baza ELSE COALESCE(BazaExigibila,0) END 
	ELSE 0 END 
	ELSE Baza 
	END BazaExigibila,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
      CASE WHEN ABS(COALESCE(TVAExigibil,0))> ABS(ValoareTVA) THEN ValoareTVA ELSE COALESCE(TVAExigibil,0) END
    ELSE 0 END 
	ELSE ValoareTVA END	TVAExigibil,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) 
	THEN Baza - CASE WHEN ABS(COALESCE(BazaExigibila,0)) > ABS(Baza) THEN Baza ELSE COALESCE(BazaExigibila,0) END  ELSE 0 END 
	ELSE 0 END BazaNeexigibila,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN ValoareTVA - 
	 CASE WHEN ABS(COALESCE(TVAExigibil,0))> ABS(ValoareTVA) THEN ValoareTVA ELSE COALESCE(TVAExigibil,0) END ELSE 0 END 
	ELSE 0 END	TVANeexigibil  
	
	
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(
		SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
		sum(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END)  Baza,
		SUM(ValoareTVA) As ValoareTVA,
		dd.CotaTVA,dd.IDRegimFiscal
		FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
		GROUP BY dd.IDDocument,dd.CotaTVA,dd.IDRegimFiscal
	) docd ON doc.IDDocument = docd.IDDocument
	JOIN RegimFiscal rf ON doc.IDRegimFiscal = rf.IDRegimFiscal
	LEFT OUTER JOIN
	(SELECT SUM(Valoare) ValPlatCuTVA,
	 SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
	 FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data>=@DeLaData AND dp1.Data<=@PanaLaData
		GROUP BY ddp1.IDDocument) selPlati ON selPlati.IDDocument = docd.IDDocument	
	
	   
	WHERE ((@cumparari=1 AND tipd.IDTipDocument in (0,10)) OR (@cumparari=0 AND tipd.IDTipDocument in (1,11))) AND doc.CodUnitate=@CodUnitate AND 
	doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData
	
	
 UNION
    --toate cele vechi neplatite
    SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal, 
	docd.*,rf.Denumire AS DenRegim,
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN ABS(COALESCE(selPlati.ValPlatCuTVA,0))> ABS(ValoareTotalaSiTVA) THEN ValoareTotalaSiTVA ELSE COALESCE(selPlati.ValPlatCuTVA,0) END 
	ELSE 0 END 
	ELSE selPlati.ValPlatCuTVA END ValPlatCuTVA, 
	 
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN ABS(COALESCE(selPlati.BazaExigibila,0)) > ABS(Baza) THEN Baza ELSE COALESCE(selPlati.BazaExigibila,0) END 
	ELSE 0 END 
	ELSE Baza END BazaExigibila,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN ABS(COALESCE(selPlati.TVAExigibil,0))> ABS(ValoareTVA) THEN ValoareTVA ELSE COALESCE(selPlati.TVAExigibil,0) END
	ELSE 0 END 
	ELSE ValoareTVA END	TVAExigibil, 
	 
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN Baza - 
		CASE WHEN  ABS(COALESCE(selPlati.BazaExigibila,0)) > ABS(Baza) THEN Baza ELSE COALESCE(selPlati.BazaExigibila,0) END  - COALESCE(selPlatiAnterior.BazaExigibila,0) ELSE 0 END 
	ELSE 0 END BazaNeexigibila,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN ValoareTVA -
	CASE WHEN ABS(COALESCE(selPlati.TVAExigibil,0))> ABS(ValoareTVA) THEN ValoareTVA ELSE COALESCE(selPlati.TVAExigibil,0) END
	 - COALESCE(selPlatianterior.TVAExigibil,0)ELSE 0 END 
	ELSE 0 END	TVANeexigibil  
	
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(
		SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
		sum(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END)  Baza,
		SUM(ValoareTVA) As ValoareTVA,
		dd.CotaTVA,dd.IDRegimFiscal
		FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
		GROUP BY dd.IDDocument,dd.CotaTVA,dd.IDRegimFiscal
	) docd ON doc.IDDocument = docd.IDDocument
	JOIN RegimFiscal rf ON doc.IDRegimFiscal = rf.IDRegimFiscal  LEFT OUTER JOIN
	(
		SELECT SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<=@PanaLaData AND dp1.Data>=@DeLaData
		GROUP BY ddp1.IDDocument
	) selPlati ON selPlati.IDDocument = docd.IDDocument LEFT OUTER JOIN 	
	(
		SELECT SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<@DeLaData 
		GROUP BY ddp1.IDDocument
	) selPlatiAnterior ON selPlatiAnterior.IDDocument = docd.IDDocument
	WHERE 
	  YEAR(doc.DataDoc) > 2012 AND                     -- dupa 2012 
	  (@cumparari=1 OR tipd.IDTipDocument in (1)) AND
	  (@cumparari=0 OR tipd.IDTipDocument in (0)) AND  -- pentru facturile de cumparare fara bonuri fiscale
	  doc.CodUnitate=@CodUnitate AND                   -- pentru unitatea curenta
	  --LTRIM(RTRIM(part.AtributFiscal))<>'' AND         -- pentru partenerii care au atribut fiscal
	  doc.DataDoc < @DeLaData AND                      -- pentru facturile de dinaintea intervalului selectat
	  (@cumparari=1 OR doc.DataDoc >= DATEADD(day,-90,@DeLaData)) AND    -- dar nu mai vechi de 90 de zile
	  (@cumparari=0 OR doc.IDRegimFiscal IN (4,17,18,19,27)) AND         -- cu regim fiscal cu TVA interne
	  (@cumparari=1 OR doc.IDRegimFiscal IN (4,9)) AND         -- cu regim fiscal cu TVA interne
	  --ValoareTotalaSiTVA >= COALESCE(ValPlatCuTVA,0) AND
      EXISTS(SELECT * FROM DocumenteDetaliu dd WHERE dd.IDDocument = docd.IDDocument AND dd.TVAIncasare=1) AND 
                                                    -- pentru facturile care au o linie cu tVA la incasare
      ABS(doc.Total) >  ABS(COALESCE((SELECT SUM(dpd.Valoare) FROM DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata = dp.IDDocumentPlata
	  WHERE dpd.IDDocument = docd.IDDocument AND dp.Data < @DeLaData),0))
												    -- care au ramas neachitate pana in perioada selectata 	
	
   ORDER BY DataDoc
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GetBalanta]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GetBalanta]
GO


CREATE PROCEDURE [dbo].[balcon_GetBalanta] 
	-- Add the parameters for the stored procedure here
	@pDtDataStartUI datetime,
	@pDtDataEndUI datetime,
	@pJurnal int,
	@pCategorie int,
	@pStrContStart TipCont,
	@pStrContEnd TipCont,
	@pCfm bit,
	@pAnalitica bit,
	@pCfmRulaje bit,
	@pTerminal bit	
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @CodUnitate smallint


	SET @CodUnitate=0
	SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()


	DECLARE @ConturiTGC AS varchar(2000)
	SET @ConturiTGC = ','
	SELECT @ConturiTGC=@ConturiTGC + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{TGC}%' AND [CodUnitate]=@CodUnitate

	DECLARE @ConturiSS AS varchar(2000)
	SET @ConturiSS = ','
	SELECT @ConturiSS=@ConturiSS + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CSS}%' AND [CodUnitate]=@CodUnitate




	SELECT  Cont,ContParinte,Terminal,Denumire,Tip,Categorie,SortValue,DebInit [SoldInitDeb],CreInit [SoldInitCre], 
		DebPrecCalc+DebitPrec [RulPrecDeb], CrePrecCalc+CreditPrec [RulPrecCre], 
		DebCurent [RulCurentDeb], CreCurent [RulCurentCre],
		DebInit + DebPrecCalc+DebitPrec+ COALESCE(DebCurent, 0) RulTotalDeb, CreInit +CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0) RulTotalCre, 
		dbo.SoldDebit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalDeb, 
		dbo.SoldCredit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalCre
		FROM
		(SELECT c.Cont, C.Denumire,C.EsteAnalitic, C.Tip,C.Categorie,C.ContParinte,C.Terminal,C.SortValue, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit],
			sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc, c.DebitPrecedent [DebitPrec], c.CreditPrecedent [CreditPrec], 
			sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
			FROM [Conturi] c LEFT OUTER JOIN 
				(SELECT sum(sumaDebitPrec) DebPrecCalc, sum(sumaCreditPrec) AS CrePrecCalc,
						sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal FROM
						(SELECT 			    
							coalesce(SUM(CASE WHEN D.DataDoc<@pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaDebitPrec,
							coalesce(SUM(CASE WHEN D.DataDoc>=@pDtDataStartUI AND D.DataDoc<=@pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
							0.00 AS sumaCreditPrec, 0.00 AS sumaCredit, 
							F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal
							FROM [RulajeDoc] D 
							INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE  d.[CodUnitate] = @CodUnitate
								AND (@pJurnal=-1 OR D.IDJurnal=@pJurnal)
							GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal
						UNION
						SELECT 
							0.00 AS sumaDebitPrec, 0.00 AS sumaDebit,
							coalesce(SUM(CASE WHEN D.DataDoc <  @pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaCreditPrec,
							coalesce(SUM(CASE WHEN D.DataDoc >= @pDtDataStartUI AND D.DataDoc<= @pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
							F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal
							FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE d.[CodUnitate] = @CodUnitate 
								AND (@pJurnal=-1 OR D.IDJurnal=@pJurnal)
							GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal
						) t1
						GROUP BY Cont,CodUnitate,IDJurnal
				) RUL ON dbo.IsContInclus(c.cont,rul.cont,c.Terminal)=1 AND c.CodUnitate=rul.CodUnitate
			WHERE  
				c.[Cont]<>'N/A' 
				AND Categorie =@pCategorie 
				AND c.[CodUnitate] =@CodUnitate 
				AND (@pStrContStart='' OR c.Cont >= @pStrContStart)
				AND (@pStrContEnd='' OR c.Cont <= @pStrContEnd)
			GROUP BY  c.Cont, C.Denumire,  C.Tip, C.Categorie, C.ContParinte,C.Terminal,C.SortValue,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent,C.EsteAnalitic
		) [balanta] 
	where
	(@pCfm=1 OR 
		(
			EXISTS(SELECT ContDebit, ContCredit FROM 
			Rulaje rj 
			JOIN  RulajeDoc rjd ON rj.IDDocument=rjd.IDDocument  
			WHERE	((ContDebit LIKE Cont + '%') OR (ContCredit LIKE Cont + '%')) 
					AND rj.[CodUnitate]=@CodUnitate 
					AND (@pJurnal=-1 OR rjd.IDJurnal = @pJurnal)
			) --exists	 
			OR (@pJurnal=-1 AND (DebInit<>0 OR CreInit<>0))
		 )
	 ) 
	AND (@pAnalitica=1 OR (([EsteAnalitic]=0)  OR (dbo.IsContIn(','+ Cont+',',@ConturiSS)<>0)))
	AND (@pCfmRulaje=1 OR (DebCurent<>0 OR CreCurent<>0))
	AND (@pTerminal= 0 OR ([Terminal]=1))
	ORDER BY Cont 
					

END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GetBalantaTGC]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GetBalantaTGC]
GO

CREATE PROCEDURE [dbo].[balcon_GetBalantaTGC] 
	-- Add the parameters for the stored procedure here
	@pDtDataStartUI datetime,
	@pDtDataEndUI datetime,
	@pJurnal int,
	@pCategorie int,
	@pStrContStart TipCont,
	@pStrContEnd TipCont,
	@pCfm bit,
	@pAnalitica bit,
	@pCfmRulaje bit,
	@pTerminal bit	
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @CodUnitate smallint


	SET @CodUnitate=0
	SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()


	DECLARE @ConturiTGC AS varchar(2000)
	SET @ConturiTGC = ','
	SELECT @ConturiTGC=@ConturiTGC + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{TGC}%' AND [CodUnitate]=@CodUnitate

	DECLARE @ConturiSS AS varchar(2000)
	SET @ConturiSS = ','
	SELECT @ConturiSS=@ConturiSS + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CSS}%' AND [CodUnitate]=@CodUnitate



	SELECT  Cont,ContParinte,Terminal,Denumire,Tip,Categorie,SortValue,DebInit [SoldInitDeb],CreInit [SoldInitCre], 
		DebPrecCalc+DebitPrec [RulPrecDeb], CrePrecCalc+CreditPrec [RulPrecCre], 
		DebCurent [RulCurentDeb], CreCurent [RulCurentCre],
		DebInit + DebPrecCalc+DebitPrec+ COALESCE(DebCurent, 0) RulTotalDeb, CreInit +CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0) RulTotalCre, 
		dbo.SoldDebit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalDeb, 
		dbo.SoldCredit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalCre
		FROM
		(SELECT c.Cont, C.Denumire,C.EsteAnalitic, C.Tip,C.Categorie,C.ContParinte,C.Terminal,C.SortValue, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit],
			sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc, c.DebitPrecedent [DebitPrec], c.CreditPrecedent [CreditPrec], 
			sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
			FROM (SELECT a.[Cont],a.[CodUnitate],a.[Denumire],a.[Tip],a.[Terminal],a.[ContParinte],a.[EsteAnalitic],a.[Categorie],a.[SortValue],sum(a.[DebitPrecedent]) DebitPrecedent ,sum(a.[CreditPrecedent]) CreditPrecedent,
					sum(b.DebitInitial) [DebitInitial] ,sum(b.creditInitial) [CreditInitial]
					FROM [Conturi] a JOIN [Conturi] b
					ON dbo.IsContInclus(a.cont,b.cont,a.Terminal)=1 and a.codunitate=b.codunitate
					where a.[CodUnitate]=@CodUnitate and a.categorie=3 and b.ContParinte='N/A'
					group by a.[Cont],a.[CodUnitate],a.[Denumire],a.[Tip],a.[Terminal],a.[ContParinte],a.[EsteAnalitic],a.[Categorie],a.[SortValue]
					UNION
					SELECT  a2.[Cont],a2.[CodUnitate],a2.[Denumire],a2.[Tip],a2.[Terminal],a2.[ContParinte],a2.[EsteAnalitic],a2.[Categorie],a2.[SortValue],a2.[DebitPrecedent],a2.[CreditPrecedent],
						a2.[DebitInitial] ,a2.[CreditInitial] FROM [Conturi] a2
					WHERE a2.codunitate=@CodUnitate and a2.categorie=0 
				 ) c  LEFT OUTER JOIN 			
				(SELECT sum(sumaDebitPrec) DebPrecCalc, sum(sumaCreditPrec) AS CrePrecCalc,
						sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal FROM
						(SELECT 			    
							coalesce(SUM(CASE WHEN D.DataDoc<@pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaDebitPrec,
							coalesce(SUM(CASE WHEN D.DataDoc>=@pDtDataStartUI AND D.DataDoc<=@pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
							0.00 AS sumaCreditPrec, 0.00 AS sumaCredit, 
							F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal
							FROM [RulajeDoc] D 
							INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE  d.[CodUnitate] = @CodUnitate
								AND (@pJurnal=-1 OR D.IDJurnal=@pJurnal)
							GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal
						UNION
						SELECT 
							0.00 AS sumaDebitPrec, 0.00 AS sumaDebit,
							coalesce(SUM(CASE WHEN D.DataDoc <  @pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaCreditPrec,
							coalesce(SUM(CASE WHEN D.DataDoc >= @pDtDataStartUI AND D.DataDoc<= @pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
							F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal
							FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE d.[CodUnitate] = @CodUnitate 
								AND (@pJurnal=-1 OR D.IDJurnal=@pJurnal)
							GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal
						) t1
						GROUP BY Cont,CodUnitate,IDJurnal
				) RUL ON dbo.IsContInclus(c.cont,rul.cont,c.Terminal)=1 AND c.CodUnitate=rul.CodUnitate
			WHERE  
				c.[Cont]<>'N/A' 
				AND Categorie in (@pCategorie,3) 
				AND c.[CodUnitate] =@CodUnitate 
				AND (@pStrContStart='' OR c.Cont >= @pStrContStart)
				AND (@pStrContEnd='' OR c.Cont <= @pStrContEnd)
			GROUP BY  c.Cont, C.Denumire,  C.Tip, C.Categorie, C.ContParinte,C.Terminal,C.SortValue,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent,C.EsteAnalitic
		) [balanta] 
	where
	(@pCfm=1 OR 
		(
			EXISTS(SELECT ContDebit, ContCredit FROM 
			Rulaje rj 
			JOIN  RulajeDoc rjd ON rj.IDDocument=rjd.IDDocument  
			WHERE	((ContDebit LIKE Cont + '%') OR (ContCredit LIKE Cont + '%')) 
					AND rj.[CodUnitate]=@CodUnitate 
					AND (@pJurnal=-1 OR rjd.IDJurnal = @pJurnal)
			) --exists	 
			OR (@pJurnal=-1 AND (DebInit<>0 OR CreInit<>0))
		 )
	 ) 
	AND (@pAnalitica=1 OR (([EsteAnalitic]=0)  OR (dbo.IsContIn(','+ Cont+',',@ConturiTGC)<>0)))
	AND (@pCfmRulaje=1 OR (DebCurent<>0 OR CreCurent<>0))
	AND (@pTerminal= 0 OR ([Terminal]=1))
	ORDER BY Cont 
					

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GetBalantaArh]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GetBalantaArh]
GO


CREATE PROCEDURE [dbo].[balcon_GetBalantaArh] 
	-- Add the parameters for the stored procedure here
	@pDtDataStartUI datetime,
	@pDtDataEndUI datetime,
	@pJurnal int,
	@pCategorie int,
	@pStrContStart TipCont,
	@pStrContEnd TipCont,
	@pCfm bit,
	@pAnalitica bit,
	@pCfmRulaje bit,
	@pTerminal bit,
	@pAn smallint	
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @CodUnitate smallint


	SET @CodUnitate=0
	SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()


	DECLARE @ConturiTGC AS varchar(2000)
	SET @ConturiTGC = ','
	SELECT @ConturiTGC=@ConturiTGC + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{TGC}%' AND [CodUnitate]=@CodUnitate

	DECLARE @ConturiSS AS varchar(2000)
	SET @ConturiSS = ','
	SELECT @ConturiSS=@ConturiSS + Cont + ',' FROM ConturiSpeciale WHERE Tipuri like'%{CSS}%' AND [CodUnitate]=@CodUnitate




	SELECT  Cont,ContParinte,Terminal,Denumire,Tip,Categorie,SortValue,DebInit [SoldInitDeb],CreInit [SoldInitCre], 
		DebPrecCalc+DebitPrec [RulPrecDeb], CrePrecCalc+CreditPrec [RulPrecCre], 
		DebCurent [RulCurentDeb], CreCurent [RulCurentCre],
		DebInit + DebPrecCalc+DebitPrec+ COALESCE(DebCurent, 0) RulTotalDeb, CreInit +CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0) RulTotalCre, 
		dbo.SoldDebit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalDeb, 
		dbo.SoldCredit(Tip, DebInit + DebPrecCalc+DebitPrec + COALESCE(DebCurent, 0), CreInit + CrePrecCalc+CreditPrec + COALESCE(CreCurent, 0)) SoldTotalCre
		FROM
		(SELECT c.Cont, C.Denumire,C.EsteAnalitic, C.Tip,C.Categorie,C.ContParinte,C.Terminal,C.SortValue, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit],
			sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc, c.DebitPrecedent [DebitPrec], c.CreditPrecedent [CreditPrec], 
			sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
			FROM [arhConturi] c LEFT OUTER JOIN 
				(SELECT sum(sumaDebitPrec) DebPrecCalc, sum(sumaCreditPrec) AS CrePrecCalc,
						sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal FROM
						(SELECT 			    
							coalesce(SUM(CASE WHEN D.DataDoc<@pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaDebitPrec,
							coalesce(SUM(CASE WHEN D.DataDoc>=@pDtDataStartUI AND D.DataDoc<=@pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
							0.00 AS sumaCreditPrec, 0.00 AS sumaCredit, 
							F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal
							FROM [arhRulajeDoc] D 
							INNER JOIN [arhRulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE  d.[CodUnitate] = @CodUnitate AND d.An = @pAn
								AND (@pJurnal=-1 OR D.IDJurnal=@pJurnal)
							GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal
						UNION
						SELECT 
							0.00 AS sumaDebitPrec, 0.00 AS sumaDebit,
							coalesce(SUM(CASE WHEN D.DataDoc <  @pDtDataStartUI THEN F.ValoareLei ELSE 0 END),0) sumaCreditPrec,
							coalesce(SUM(CASE WHEN D.DataDoc >= @pDtDataStartUI AND D.DataDoc<= @pDtDataEndUI THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
							F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal
							FROM [arhRulajeDoc] D 
							INNER JOIN [arhRulaje] F ON D.[IDDocument] = F.[IDDocument] 
							WHERE d.[CodUnitate] = @CodUnitate AND d.An = @pAn
								AND (@pJurnal=-1 OR D.IDJurnal=@pJurnal)
							GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal
						) t1
						GROUP BY Cont,CodUnitate,IDJurnal
				) RUL ON dbo.IsContInclus(c.cont,rul.cont,c.Terminal)=1 AND c.CodUnitate=rul.CodUnitate
			WHERE  
				c.[Cont]<>'N/A' 
				AND c.[An]=@pAn
				AND Categorie =@pCategorie 
				AND c.[CodUnitate] =@CodUnitate 
				AND (@pStrContStart='' OR c.Cont >= @pStrContStart)
				AND (@pStrContEnd='' OR c.Cont <= @pStrContEnd)
			GROUP BY  c.Cont, C.Denumire,  C.Tip, C.Categorie, C.ContParinte,C.Terminal,C.SortValue,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent,C.EsteAnalitic
		) [balanta] 
	where
	(@pCfm=1 OR 
		(
			EXISTS(SELECT ContDebit, ContCredit FROM 
			arhRulaje rj 
			JOIN  arhRulajeDoc rjd ON rj.IDDocument=rjd.IDDocument  
			WHERE	((ContDebit LIKE Cont + '%') OR (ContCredit LIKE Cont + '%')) 
					AND rj.[CodUnitate]=@CodUnitate AND rjd.An = @pAn
					AND (@pJurnal=-1 OR rjd.IDJurnal = @pJurnal)
			) --exists	 
			OR (@pJurnal=-1 AND (DebInit<>0 OR CreInit<>0))
		 )
	 ) 
	AND (@pAnalitica=1 OR (([EsteAnalitic]=0)  OR (dbo.IsContIn(','+ Cont+',',@ConturiSS)<>0)))
	AND (@pCfmRulaje=1 OR (DebCurent<>0 OR CreCurent<>0))
	AND (@pTerminal= 0 OR ([Terminal]=1))
	ORDER BY Cont 
					

END
GO






IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GenListaBalanta]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GenListaBalanta]
GO


CREATE PROCEDURE [dbo].[balcon_GenListaBalanta]
	@An smallint   
AS

DECLARE @dataUI as float, @dtDataStartUI as datetime,@dtDataEndUI as datetime
DECLARE @strContStart as TipCont,@strContEnd as TipCont
DECLARE @strDataStart as varchar(50), @strDataEnd as varchar(50)
DECLARE @tabela varchar(100)
DECLARE @categorie as tinyint
DECLARE @strcategorie as varchar(10)
DECLARE @analitica bit
DECLARE @cfm bit
DECLARE @dataStartFunctie varchar(50)
DECLARE @jurnal int
DECLARE @dataEndFunctie varchar(50)
DECLARE @ultimaData datetime
DECLARE @ptgc bit


--determina ultima data la care s-a lucrat
SELECT @ultimaData= MAX(DataDoc) FROM RulajeDoc


SET @categorie = 0
SET @analitica = 0
SET @cfm = 0
SET @ptgc = 0


DECLARE @tipb as varchar(100)
DECLARE @idLista as int;
SET @idLista = 1

DECLARE @CodUnitate smallint
SET @CodUnitate=0

SET @tipb='0'
SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()
PRINT('CodUnitate ''' + cast(@CodUnitate as varchar(20)) + '''')
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='TPB' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
IF @tipb='Extra Bilantiera'
BEGIN
	SET @categorie = 2			
END

SET @tipb='0'
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='ANL' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
if @tipb='1'
	SET @analitica =1
SET @tipb='0'
--conturi fara miscari
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='CFM' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
if @tipb='1'
	SET @cfm =1

SET @jurnal= -1
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='JRN' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
if @tipb<>''
	SELECT @jurnal = IDJurnal FROM [Jurnale] WHERE Codificare=@tipb
 
print(@jurnal)
SELECT @strDataEnd=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='LDT' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
SET @strDataEnd = RTRIM(LTRIM(@strDataEnd))

SET @tipb='0'
SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='TGC' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
if @tipb='1'
	SET @ptgc =1



IF LEN(@strDataEnd)<>0
BEGIN
	SET @dtDataEndUI = CAST(CAST(@strDataEnd AS float)-2 AS datetime)
	SET @strDataEnd = ''''+CAST(@dtDataEndUI AS varchar(50))+''''
	SET @dataEndFunctie = ''''+CAST(YEAR(@dtDataEndUI) as varchar(4))+'-'+CAST(MONTH(@dtDataEndUI) AS varchar(2))+'-'+CAST(DAY(@dtDataEndUI) AS varchar(2))+''''
	SET @dataStartFunctie = ''''+CAST(YEAR(@dtDataEndUI) as varchar(4))+'-'+CAST(MONTH(@dtDataEndUI) AS varchar(2))+'-01'''
    SET @dtDataStartUI = DATEADD(Day, 1-DAY(@dtDataEndUI),@dtDataEndUI)
END
ELSE
BEGIN
	SET @dataEndFunctie = ''''+CAST(@ultimaData AS varchar(50))+''''
	SET @dataStartFunctie = ''''+CAST(YEAR(@ultimaData) as varchar(4))+'-'+CAST(MONTH(@ultimaData) AS varchar(2))+'-01'''
END


SELECT @strContStart=LTRIM(RTRIM(Valoare)) FROM [ParamListe] WHERE IDLista=@idLista AND Cod='DLC' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
SELECT @strContEnd=LTRIM(RTRIM(Valoare)) FROM [ParamListe] WHERE IDLista=@idLista AND Cod='PLC' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
--IF @strContEnd = ''
 -- SET @strContEnd = REPLICATE('Z',50)


DECLARE	@return_value int
PRINT('DECLARE @strContStart as TipCont,@strContEnd as TipCont
DECLARE @strDataStart as varchar(50), @strDataEnd as varchar(50)
DECLARE @tabela varchar(100)
DECLARE @categorie as tinyint
DECLARE @strcategorie as varchar(10)
DECLARE @analitica bit
DECLARE @cfm bit
DECLARE @dataStartFunctie varchar(50)
DECLARE @jurnal int
DECLARE @dataEndFunctie varchar(50)
DECLARE @ultimaData datetime
DECLARE @pAn smallint
DECLARE @ptgc bit
')
PRINT('SET @DtDataStartUI = ''' + cast(@DtDataStartUI as varchar(20)) + '''')
PRINT('SET @dtDataEndUI=' + cast(@dtDataEndUI as varchar(100)) + '''')
PRINT('SET @jurnal=' + cast(@jurnal as varchar(100)))
PRINT('SET @categorie=' + cast(@categorie as varchar(100)))
PRINT('SET @strContStart=''' + cast(@strContStart as varchar(100)) +'''')
PRINT('SET @StrContEnd =' + cast(@StrContEnd as varchar(100)) + '''')
PRINT('SET @cfm=' + cast(@cfm as varchar(100)))
PRINT('SET @analitica=' + cast(@analitica as varchar(100)))
PRINT('SET @pAn=' + cast(@An as varchar(100)))
PRINT('SET @ptgc=' + cast(@ptgc as varchar(100)))

IF @An=0
BEGIN
	IF @ptgc = 1
		EXEC	@return_value = [dbo].[balcon_GetBalantaTGC]
		@pDtDataStartUI = @DtDataStartUI,
		@pDtDataEndUI = @dtDataEndUI,
		@pJurnal = @jurnal,
		@pCategorie = @categorie,
		@pStrContStart = @strContStart,
		@pStrContEnd = @StrContEnd,
		@pCfm = @cfm,
		@pAnalitica = @analitica,
		@pCfmRulaje = 1,
		@pTerminal = 0
	ELSE
		EXEC	@return_value = [dbo].[balcon_GetBalanta]
		@pDtDataStartUI = @DtDataStartUI,
		@pDtDataEndUI = @dtDataEndUI,
		@pJurnal = @jurnal,
		@pCategorie = @categorie,
		@pStrContStart = @strContStart,
		@pStrContEnd = @StrContEnd,
		@pCfm = @cfm,
		@pAnalitica = @analitica,
		@pCfmRulaje = 1,
		@pTerminal = 0
END
ELSE
		EXEC	@return_value = [dbo].[balcon_GetBalantaArh]
		@pDtDataStartUI = @DtDataStartUI,
		@pDtDataEndUI = @dtDataEndUI,
		@pJurnal = @jurnal,
		@pCategorie = @categorie,
		@pStrContStart = @strContStart,
		@pStrContEnd = @StrContEnd,
		@pCfm = @cfm,
		@pAnalitica = @analitica,
		@pCfmRulaje = 1,
		@pTerminal = 0,
		@pAn = @An


GOTO Gata
EROARE:
	RAISERROR ('Eroare la generarea balantei',16,-1)
Gata:

GO




IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CopiazaAnInchis]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CopiazaAnInchis]
GO

CREATE PROCEDURE [dbo].[CopiazaAnInchis]  
@pAn as int as
BEGIN

DECLARE @strAn varchar(4)
DECLARE @Comanda varchar(5000)

SET @strAn=CAST(@pAn AS varchar(4))

BEGIN TRAN 
PRINT('ArticoleAliniate')
--ArticoleAliniate---------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ArticoleAliniate'+@strAn+']') AND type in (N'U'))
BEGIN
	SET @Comanda = 'INSERT INTO [arhArticoleAliniate] SELECT a.*,'+@strAn+' FROM [ArticoleAliniate'+@strAn+'] a '
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE
END
PRINT('Bilant')
--Bilant---------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Bilant'+@strAn+']') AND type in (N'U'))
BEGIN
	SET @Comanda = 'INSERT INTO [arhBilant] SELECT a.*,'+@strAn+' FROM [Bilant'+@strAn+'] a ' 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE
END

PRINT('Ordonantari')
--Bilant---------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Ordonantari'+@strAn+']') AND type in (N'U'))
BEGIN
	SET @Comanda = 'INSERT INTO [arhOrdonantari] SELECT a.*,'+@strAn+' FROM [Ordonantari'+@strAn+'] a ' 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE
END

PRINT('Conturi')
--Conturi---------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Conturi'+@strAn+']') AND type in (N'U'))
BEGIN
	SET @Comanda = 'INSERT INTO [arhConturi]([Cont],[CodUnitate],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit],[Sold],[An]) SELECT [Cont],[CodUnitate],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit],[Sold],'+@strAn+' FROM [Conturi'+@strAn+'] ' 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE
END

PRINT('Conturi Valuta')
--ConturiValuta---------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConturiValuta'+@strAn+']') AND type in (N'U'))
BEGIN
	SET @Comanda = 'INSERT INTO [arhConturiValuta] SELECT a.*,'+@strAn+' FROM [ConturiValuta'+@strAn+'] a ' 
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE
END

PRINT('JurnaleSolduriInitiale')
--JurnaleSolduriInitiale---------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JurnaleSolduriInitiale'+@strAn+']') AND type in (N'U'))
BEGIN
	SET @Comanda = 'INSERT INTO [arhJurnaleSolduriInitiale] SELECT a.*,'+@strAn+' FROM [JurnaleSolduriInitiale'+@strAn+'] a'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE
END

PRINT('RezultatPatrimonial')
--RezultatPatrimonial---------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RezultatPatrimonial'+@strAn+']') AND type in (N'U'))
BEGIN
	SET @Comanda = 'INSERT INTO [arhRezultatPatrimonial] SELECT a.*,'+@strAn+' FROM [RezultatPatrimonial'+@strAn+'] a'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE
END


PRINT('RulajeDoc')
--RulajeDoc---------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RulajeDoc'+@strAn+']') AND type in (N'U'))
BEGIN
	SET @Comanda = 'INSERT INTO [arhRulajeDoc] SELECT a.*,'+@strAn+' FROM [RulajeDoc'+@strAn+'] a'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE
END

PRINT('Rulaje')
--Rulaje---------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Rulaje'+@strAn+']') AND type in (N'U'))
BEGIN
	SET @Comanda = 'INSERT INTO [arhRulaje] SELECT a.*,'+@strAn+' FROM [Rulaje'+@strAn+'] a'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE
END

PRINT('VenituriSiCheltuieli')
--VenituriSiCheltuieli---------------------------------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VenituriSiCheltuieli'+@strAn+']') AND type in (N'U'))
BEGIN
	SET @Comanda = 'INSERT INTO [arhVenituriSiCheltuieli] SELECT a.*,'+@strAn+' FROM [VenituriSiCheltuieli'+@strAn+'] a'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
END	


PRINT('Sterge Ordonantari')
SET @Comanda = 'DROP TABLE [Ordonantari'+@strAn+']'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

PRINT('Sterge rulaje')
SET @Comanda = 'DROP TABLE [Rulaje'+@strAn+']'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

PRINT('Sterge rulajeDoc')
SET @Comanda = 'DROP TABLE [RulajeDoc'+@strAn+']'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DROP TABLE [ConturiValuta'+@strAn+']' 
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DROP Table [JurnaleSolduriInitiale'+@strAn+']'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

--SET @Comanda = 'DELETE FROM [Ordonantari] WHERE YEAR(DataDoc)='+@strAn +' AND [CodUnitate]=' + @strCodUnitate
--EXEC(@Comanda) 
--IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DROP TABLE [RezultatPatrimonial'+@strAn+']'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE


SET @Comanda = 'DROP TABLE [VenituriSiCheltuieli'+@strAn+']'
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DROP TABLE [Bilant'+@strAn+'] ' 
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DROP TABLE [Conturi'+@strAn+']' 
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE

SET @Comanda = 'DROP TABLE [ArticoleAliniate'+@strAn+']' 
EXEC(@Comanda) 
IF @@ERROR != 0 GOTO EROARE


COMMIT

GOTO Gata
EROARE:
	ROLLBACK
	RAISERROR ('Eroare la inchiderea anului!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
Gata:

END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GenRegistruCasaArhiva]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GenRegistruCasaArhiva]
GO

CREATE PROCEDURE [dbo].[balcon_GenRegistruCasaArhiva]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@Cod varchar(50),
	@DataDocJust bit,
	@an smallint
AS
BEGIN

	SET NOCOUNT ON;
	

	SELECT ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc,
		  ct.DebitInitial - ct.CreditInitial + 
		(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM arhRulaje rlj JOIN arhRulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContDebit = ct.Cont) AND rjd.DataDoc<rd.DataDoc  and ct.An=rjd.An)
		-(SELECT COALESCE(SUM(rlj.ValoareLei),0) FROM arhRulaje rlj JOIN arhRulajeDoc rjd ON rlj.IDDocument = rjd.IDDocument
		WHERE rlj.CodUnitate = ct.CodUnitate AND (rlj.ContCredit = ct.Cont) AND rjd.DataDoc<rd.DataDoc and ct.An=rjd.An) 
			sodlzi,
		SUM(CASE WHEN ruld.ContDebit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajDebit,
		SUM(CASE WHEN ruld.ContCredit=ct.Cont THEN ruld.ValoareLei ELSE 0 END) rulajCredit
		FROM [arhConturi] ct 
		LEFT OUTER JOIN [ConturiSpeciale] cs ON ct.Cont = cs.Cont AND ct.CodUnitate = cs.CodUnitate 
		JOIN [arhRulaje] ruld ON ct.CodUnitate=ruld.CodUnitate AND (ruld.ContDebit = ct.Cont OR ruld.ContCredit=ct.Cont) and ct.An=ruld.An
		JOIN [arhRulajeDoc] rd ON ruld.IDDocument = rd.IDDocument and ct.An=rd.An
		WHERE 
		ct.An=@An AND 
		ct.CodUnitate = @CodUnitate AND rd.DataDoc>=@deladata AND rd.DataDoc<=@PanaLaData
		AND (LEN(@Cod)=0 OR cs.Tipuri LIKE @Cod)
		GROUP BY ct.CodUnitate,ct.DebitInitial,ct.CreditInitial,rd.DataDoc,ct.Cont,ct.An
	
		
END

GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GenNotaContabilaUnion]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GenNotaContabilaUnion]
GO

CREATE PROCEDURE [dbo].[balcon_GenNotaContabilaUnion]
	@cont TipCont,
	@CodUnitate smallint,
	@DeLaData datetime,
	@PanaLaData datetime,
	@An smallint,
	@strFiltruCont varchar(2000),
	@strFiltruNrNota varchar(2000),
	@strFiltruJurnal varchar(2000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	if @An <= 0
	 BEGIN
		SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.ContDebit, a.ContCredit, 
						  b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
						  b.TipFormula, c.Categorie, 'D' AS Tip, a.CodUnitate
					FROM  [Rulaje] a INNER JOIN  [RulajeDoc] b ON a.IDDocument = b.IDDocument 
					INNER JOIN  [Conturi] c ON a.ContDebit = c.Cont AND c.CodUnitate = a.CodUnitate
					WHERE [ContDebit]<>'N/A' AND (a.CodUnitate = @CodUnitate) AND
					(@cont = '' OR (a.ContDebit like  @cont + '%' OR a.ContCredit like @cont + '%'))
					AND (b.DataDoc >=@DeLaData AND b.DataDoc<= @PanaLaData)
					AND (LEN(LTRIM(RTRIM(@strFiltruNrNota)))=0 OR (b.NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruNrNota,',') fart)))
					AND (LEN(LTRIM(RTRIM(@strFiltruCont)))=0 OR (ContDebit IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruCont,',') fart)))
					AND (LEN(LTRIM(RTRIM(@strFiltruJurnal)))=0 OR (b.IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruJurnal,',') fart)))
		UNION 
		SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.[ContCredit] AS [ContDebit], a.[ContDebit] AS ContCredit, 
						  b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
						  b.TipFormula, c.Categorie, 'C' AS Tip, a.CodUnitate
					FROM  [Rulaje] a INNER JOIN  [RulajeDoc] b ON a.IDDocument = b.IDDocument 
					INNER JOIN  [Conturi] c ON a.ContDebit = c.Cont AND c.CodUnitate = a.CodUnitate
					WHERE [ContCredit]<>'N/A' AND (a.CodUnitate = @CodUnitate) 
					AND (@cont= '' OR (a.ContDebit like @cont + '%' OR a.ContCredit like @cont + '%'))
					AND (b.DataDoc >=@DeLaData AND b.DataDoc<= @PanaLaData)
					AND (LEN(LTRIM(RTRIM(@strFiltruNrNota)))=0 OR (b.NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruNrNota,',') fart)))	
					AND (LEN(LTRIM(RTRIM(@strFiltruCont)))=0 OR (ContCredit IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruCont,',') fart)))
					AND (LEN(LTRIM(RTRIM(@strFiltruJurnal)))=0 OR (b.IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruJurnal,',') fart)))
		ORDER BY b.DataDoc
	END
	ELSE
	BEGIN
		SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.ContDebit, a.ContCredit, 
						  b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
						  b.TipFormula, c.Categorie, 'D' AS Tip, a.CodUnitate
					FROM  [arhRulaje] a INNER JOIN  [arhRulajeDoc] b ON a.IDDocument = b.IDDocument 
					INNER JOIN  [arhConturi] c ON a.ContDebit = c.Cont AND c.CodUnitate = a.CodUnitate AND a.An=c.An
					WHERE [ContDebit]<>'N/A' AND (a.CodUnitate = @CodUnitate) AND
					a.An = @An AND
					(@cont = '' OR (a.ContDebit like  @cont + '%' OR a.ContCredit like @cont + '%'))
					AND (b.DataDoc >=@DeLaData AND b.DataDoc<= @PanaLaData)
					AND (LEN(LTRIM(RTRIM(@strFiltruNrNota)))=0 OR (b.NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruNrNota,',') fart)))
					AND (LEN(LTRIM(RTRIM(@strFiltruCont)))=0 OR (ContDebit IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruCont,',') fart)))
					AND (LEN(LTRIM(RTRIM(@strFiltruJurnal)))=0 OR (b.IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruJurnal,',') fart)))
		UNION 
		SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.[ContCredit] AS [ContDebit], a.[ContDebit] AS ContCredit, 
						  b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
						  b.TipFormula, c.Categorie, 'C' AS Tip, a.CodUnitate
					FROM  [arhRulaje] a INNER JOIN  [arhRulajeDoc] b ON a.IDDocument = b.IDDocument 
					INNER JOIN  [arhConturi] c ON a.ContDebit = c.Cont AND c.CodUnitate = a.CodUnitate  AND a.An=c.An
					WHERE [ContCredit]<>'N/A' AND (a.CodUnitate = @CodUnitate) and
					a.An = @An 
					AND (@cont= '' OR (a.ContDebit like @cont + '%' OR a.ContCredit like @cont + '%'))
					AND (b.DataDoc >=@DeLaData AND b.DataDoc<= @PanaLaData)
					AND (LEN(LTRIM(RTRIM(@strFiltruNrNota)))=0 OR (b.NumarNota NOT IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruNrNota,',') fart)))	
					AND (LEN(LTRIM(RTRIM(@strFiltruCont)))=0 OR (ContCredit IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruCont,',') fart)))
					AND (LEN(LTRIM(RTRIM(@strFiltruJurnal)))=0 OR (b.IDJurnal IN (SELECT fart.value FROM dbo.SplitParameters(@strFiltruJurnal,',') fart)))
		ORDER BY b.DataDoc
	
	END

END

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[amix_GetObiecteInventar]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[amix_GetObiecteInventar]
GO



CREATE PROCEDURE [dbo].[amix_GetObiecteInventar]
	@CodUnitate smallint,
	@FiltruDep varchar(8000),
	@FiltruCont varchar(8000),
	@Luna smallint,
	@Anul smallint
AS
BEGIN
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT ar.*,um.Prescurtare UMDenumire,dp.Nume LocDepozitare,dp2.Nume NumeGestiune FROM AmixRegistru ar JOIN UnitatiMasura um ON ar.IDUnitateMasura = um.IDUnitateMasura
	JOIN Departamente dp ON ar.IDDepartament=dp.IDDepartament AND dp.CodUnitate = ar.CodUnitate
	LEFT OUTER JOIN	Departamente dp2 ON dp.IDDepartParinte = dp2.IDDepartament AND dp.CodUnitate = dp2.CodUnitate
	Where ar.CodUnitate = @CodUnitate AND ar.Compus >=3 AND
	(LEN(LTRIM(RTRIM(@FiltruDep)))=0 OR (ar.IDDepartament IN (SELECT fart.value FROM dbo.SplitIntParameters(@FiltruDep,',') fart))) AND
	(LEN(LTRIM(RTRIM(@FiltruCont)))=0 OR (ar.ContTransferForSuperior IN (SELECT fart.value FROM dbo.SplitIntParameters(@FiltruCont,',') fart)))
	AND (YEAR([DataIntrare])*100 + MONTH([DataIntrare])) <= (@Anul*100 + @Luna)
    AND ar.InFolosinta=1
END

GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaJurnalVanzariTVALaIncasare]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaJurnalVanzariTVALaIncasare]
GO


CREATE PROCEDURE [dbo].[fares_GenereazaJurnalVanzariTVALaIncasare] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@cumpTI bit
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
	CASE WHEN CuTVALaIncasare=1 THEN NrDataDocPlata ELSE '' END NrDataDocPlata,
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(ValPlatCuTVA,0) ELSE 0 END ValPlatCuTVA, 
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(BazaExigibila,0) ELSE 0 END BazaExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(TVAExigibil,0) ELSE 0 END TVAExigibil,
	CASE WHEN CuTVALaIncasare=1 THEN doc.TotalValoare - Coalesce(BazaExigibila,0) ELSE 0 END BazaNeExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN TotalValoareTVA  - Coalesce(TVAExigibil,0) ELSE 0 END TVANeExigibila,	
	docd.*
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=11 THEN ValoareTotala ELSE ValoareTotala+ValoareTVA END ) ValoareTotalaSiTVA,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN ValoareTVA ELSE 0 END) TVA24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN ValoareTVA ELSE 0 END) TVA19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN ValoareTVA ELSE 0 END) TVA,	  
	  SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) InversBaza,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN ValoareTVA ELSE 0 END) InversTVA,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (61) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) OSRSTVA,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (62) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) PISCDD,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (63) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) PISFDD,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (64,14) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) LIBSCA143ad,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (65,15) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) LIBSCA143bc,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (66) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) EALBPSSDD,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (67) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) LPSFDD,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (68) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) ONRA,
	  CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END TI,
	  CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END CuTVALaIncasare
	   FROM DocumenteDetaliu dd join Documente a on a.IDDocument=dd.IDDocument
	   GROUP BY dd.IDDocument,CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END,CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END) docd ON doc.IDDocument = docd.IDDocument
	   LEFT OUTER JOIN
		(SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		 SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		 FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
			WHERE dp1.Data>=@DeLaData AND dp1.Data<=@PanaLaData
			GROUP BY ddp1.IDDocument) selPlati ON selPlati.IDDocument = docd.IDDocument	
			
	   WHERE ((tipd.IDTipDocument in (1,11)) OR (@cumpTI=1 AND tipd.IDTipDocument in (0) AND TI=1))
		--AND LTRIM(RTRIM(part.AtributFiscal))<>''
		AND doc.CodUnitate=@CodUnitate 
		AND doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData
		
	UNION	---neexigibile mai mici de 90 de zile
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
	CASE WHEN CuTVALaIncasare=1 THEN selPlati.NrDataDocPlata ELSE '' END NrDataDocPlata,
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(selPlati.ValPlatCuTVA,0) ELSE 0 END ValPlatCuTVA, 
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(selPlati.BazaExigibila,0) ELSE 0 END BazaExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN  Coalesce(selPlati.TVAExigibil,0)  ELSE 0 END TVAExigibil,
	CASE WHEN CuTVALaIncasare=1 THEN  doc.TotalValoare - COALESCE(selPlatiAnterior.BazaExigibila,0) -  Coalesce(selPlati.BazaExigibila,0) ELSE 0 END BazaNeExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN  TotalValoareTVA  - COALESCE(selPlatiAnterior.TVAExigibil,0) - Coalesce(selPlati.TVAExigibil,0) ELSE 0 END TVANeExigibila,
	docd.*
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=11 THEN ValoareTotala ELSE ValoareTotala+ValoareTVA END ) ValoareTotalaSiTVA,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN ValoareTVA ELSE 0 END) TVA24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN ValoareTVA ELSE 0 END) TVA19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN ValoareTVA ELSE 0 END) TVA,
	  
	  0 InversBaza,0 InversTVA,
	  0 OSRSTVA,
	  0 PISCDD,
	  0 PISFDD,
	  0 LIBSCA143ad,
	  0 LIBSCA143bc,
	  0 EALBPSSDD,
	  0 LPSFDD,
	  0 ONRA,
	  CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END TI,
	  CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END CuTVALaIncasare
	   FROM DocumenteDetaliu dd join Documente a on a.IDDocument=dd.IDDocument
	   GROUP BY dd.IDDocument,CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END,CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END) docd ON doc.IDDocument = docd.IDDocument
	   LEFT OUTER JOIN 
	(
		SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<=@PanaLaData
		GROUP BY ddp1.IDDocument
	) selPlati ON selPlati.IDDocument = docd.IDDocument LEFT OUTER JOIN 	
	(
		SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<@DeLaData 
		GROUP BY ddp1.IDDocument
	) selPlatiAnterior ON selPlatiAnterior.IDDocument = docd.IDDocument
			
	WHERE
	YEAR(doc.DataDoc) > 2012 AND                     -- dupa 2012  
	((tipd.IDTipDocument in (1)) OR (@cumpTI=1 AND tipd.IDTipDocument in (0) AND TI=1)) -- pentru facturile de vanzare fara bonuri fiscale
	AND doc.CodUnitate=@CodUnitate AND													-- pentru unitatea curenta
    --LTRIM(RTRIM(part.AtributFiscal))<>'' AND										    -- pentru partenerii care au atribut fiscal
    DATEADD(day,90,doc.DataDoc) > @PanaLaData AND										-- dar nu mai vechi de 90 de zile
    doc.DataDoc<@DeLaData AND															-- pentru facturile de dinaintea intervalului selectat
	doc.IDRegimFiscal IN (4,9) AND
    --ValoareTotalaSiTVA > COALESCE(ValPlatCuTVA,0)
    EXISTS(SELECT * FROM DocumenteDetaliu dd WHERE dd.IDDocument = docd.IDDocument AND dd.TVAIncasare=1) AND 
																						-- pentru facturile care au o linie cu tVA la incasare
    ABS(doc.Total) >  ABS(COALESCE((SELECT SUM(dpd.Valoare) FROM DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata = dp.IDDocumentPlata
	WHERE dpd.IDDocument = docd.IDDocument AND dp.Data < @DeLaData),0))
																						-- care au ramas neachitate pana in perioada selectata 

    --CELE de 90 de zile devin exigibile in perioada curenta
	UNION	
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
	CASE WHEN CuTVALaIncasare=1 THEN selPlati.NrDataDocPlata ELSE '' END NrDataDocPlata,
	CASE WHEN CuTVALaIncasare=1 THEN Coalesce(selPlati.ValPlatCuTVA,0) ELSE 0 END ValPlatCuTVA, 
	CASE WHEN CuTVALaIncasare=1 THEN doc.TotalValoare - COALESCE(selPlatiAnterior.BazaExigibila,0) ELSE 0 END BazaExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN  TotalValoareTVA  - COALESCE(selPlatiAnterior.TVAExigibil,0)  ELSE 0 END TVAExigibil,
	CASE WHEN CuTVALaIncasare=1 THEN  0 ELSE 0 END BazaNeExigibila,
	CASE WHEN CuTVALaIncasare=1 THEN  0 ELSE 0 END TVANeExigibila,
	docd.*
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=11 THEN ValoareTotala ELSE ValoareTotala+ValoareTVA END ) ValoareTotalaSiTVA,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=24 THEN ValoareTVA ELSE 0 END) TVA24,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=19 THEN ValoareTVA ELSE 0 END) TVA19,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN CASE WHEN IDTipDocument=11 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza,
	  SUM(CASE WHEN dd.IDRegimFiscal IN (9,4) THEN ValoareTVA ELSE 0 END) TVA,
	  
	  0 InversBaza,0 InversTVA,
	  0 OSRSTVA,
	  0 PISCDD,
	  0 PISFDD,
	  0 LIBSCA143ad,
	  0 LIBSCA143bc,
	  0 EALBPSSDD,
	  0 LPSFDD,
	  0 ONRA,
	  CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END TI,
	  CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END CuTVALaIncasare
	   FROM DocumenteDetaliu dd join Documente a on a.IDDocument=dd.IDDocument
	   GROUP BY dd.IDDocument,CASE WHEN dd.IDRegimFiscal IN (6) THEN 1 ELSE 0 END,CASE WHEN dd.IDRegimFiscal IN (9,4) THEN 1 ELSE 0 END) docd ON doc.IDDocument = docd.IDDocument
	   LEFT OUTER JOIN 
	(
		SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<=@PanaLaData
		GROUP BY ddp1.IDDocument
	) selPlati ON selPlati.IDDocument = docd.IDDocument LEFT OUTER JOIN 	
	(
		SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<@DeLaData 
		GROUP BY ddp1.IDDocument
	) selPlatiAnterior ON selPlatiAnterior.IDDocument = docd.IDDocument
			
	WHERE
	YEAR(doc.DataDoc) > 2012 AND                     -- dupa 2012  
	((tipd.IDTipDocument in (1)) OR (@cumpTI=1 AND tipd.IDTipDocument in (0) AND TI=1)) -- pentru facturile de vanzare fara bonuri fiscale
	AND doc.CodUnitate=@CodUnitate AND													-- pentru unitatea curenta
    --LTRIM(RTRIM(part.AtributFiscal))<>'' AND										    -- pentru partenerii care au atribut fiscal
    DATEADD(day,90,doc.DataDoc) >= @DeLaData AND DATEADD(day,90,doc.DataDoc) <= @PanaLaData AND		-- dar nu mai vechi de 90 de zile
    doc.DataDoc<@DeLaData AND															-- pentru facturile de dinaintea intervalului selectat
	doc.IDRegimFiscal IN (4,9) AND
    --ValoareTotalaSiTVA > COALESCE(ValPlatCuTVA,0)
    EXISTS(SELECT * FROM DocumenteDetaliu dd WHERE dd.IDDocument = docd.IDDocument AND dd.TVAIncasare=1) AND 
																						-- pentru facturile care au o linie cu tVA la incasare
    ABS(doc.Total) >  ABS(COALESCE((SELECT SUM(dpd.Valoare) FROM DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata = dp.IDDocumentPlata
	WHERE dpd.IDDocument = docd.IDDocument AND dp.Data < @DeLaData),0))
																						-- care au ramas neachitate pana in perioada selectata 

	
	ORDER BY DataDoc
END


GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fares_GenereazaJurnalVerticalTVALaIncasare]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[fares_GenereazaJurnalVerticalTVALaIncasare]
GO


CREATE PROCEDURE [dbo].[fares_GenereazaJurnalVerticalTVALaIncasare] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@cumparari smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal,
	docd.*,rf.Denumire AS DenRegim,
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN COALESCE(ValPlatCuTVA,0)> ValoareTotalaSiTVA THEN ValoareTotalaSiTVA ELSE COALESCE(ValPlatCuTVA,0) END 
	ELSE 0 END 
	ELSE ValPlatCuTVA END ValPlatCuTVA,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
	  CASE WHEN ABS(COALESCE(BazaExigibila,0)) > ABS(Baza) THEN Baza ELSE COALESCE(BazaExigibila,0) END 
	ELSE 0 END 
	ELSE Baza 
	END BazaExigibila,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
      CASE WHEN ABS(COALESCE(TVAExigibil,0))> ABS(ValoareTVA) THEN ValoareTVA ELSE COALESCE(TVAExigibil,0) END
    ELSE 0 END 
	ELSE ValoareTVA END	TVAExigibil,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) 
	THEN Baza - CASE WHEN ABS(COALESCE(BazaExigibila,0)) > ABS(Baza) THEN Baza 
	ELSE COALESCE(BazaExigibila,0) END  ELSE 0 END 
	ELSE 0 END BazaNeexigibila,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN ValoareTVA - 
	 CASE WHEN ABS(COALESCE(TVAExigibil,0))> ABS(ValoareTVA) THEN ValoareTVA ELSE COALESCE(TVAExigibil,0) END ELSE 0 END 
	ELSE 0 END	TVANeexigibil  
	
	
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(
		SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
		sum(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END)  Baza,
		SUM(ValoareTVA) As ValoareTVA,
		dd.CotaTVA,dd.IDRegimFiscal
		FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
		GROUP BY dd.IDDocument,dd.CotaTVA,dd.IDRegimFiscal
	) docd ON doc.IDDocument = docd.IDDocument
	JOIN RegimFiscal rf ON doc.IDRegimFiscal = rf.IDRegimFiscal
	LEFT OUTER JOIN
	(SELECT SUM(Valoare) ValPlatCuTVA,
	 SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
	 FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data>=@DeLaData AND dp1.Data<=@PanaLaData
		GROUP BY ddp1.IDDocument) selPlati ON selPlati.IDDocument = docd.IDDocument	
	
	   
	WHERE ((@cumparari=1 AND tipd.IDTipDocument in (0,10)) OR (@cumparari=0 AND tipd.IDTipDocument in (1,11))) AND doc.CodUnitate=@CodUnitate AND 
	doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData
	
	
 UNION
    --toate cele vechi neplatite de 90 de zile
    SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal, 
	docd.*,rf.Denumire AS DenRegim,
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN ABS(COALESCE(selPlati.ValPlatCuTVA,0))> ABS(ValoareTotalaSiTVA) THEN ValoareTotalaSiTVA ELSE COALESCE(selPlati.ValPlatCuTVA,0) END 
	ELSE 0 END 
	ELSE selPlati.ValPlatCuTVA END ValPlatCuTVA, 
	 
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN ABS(COALESCE(selPlati.BazaExigibila,0)) > ABS(Baza) THEN Baza ELSE COALESCE(selPlati.BazaExigibila,0) END 
	ELSE 0 END 
	ELSE Baza END BazaExigibila,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN ABS(COALESCE(selPlati.TVAExigibil,0))> ABS(ValoareTVA) THEN ValoareTVA ELSE COALESCE(selPlati.TVAExigibil,0) END
	ELSE 0 END 
	ELSE ValoareTVA END	TVAExigibil, 
	 
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN Baza - CASE WHEN 
	ABS(COALESCE(selPlati.BazaExigibila,0)) > ABS(Baza) THEN Baza ELSE COALESCE(selPlati.BazaExigibila,0) END  - COALESCE(selPlatiAnterior.BazaExigibila,0) ELSE 0 END 
	ELSE 0 END BazaNeexigibila,  
	
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN ValoareTVA -
	CASE WHEN ABS(COALESCE(selPlati.TVAExigibil,0))> ABS(ValoareTVA) THEN ValoareTVA ELSE COALESCE(selPlati.TVAExigibil,0) END
	 - COALESCE(selPlatianterior.TVAExigibil,0)ELSE 0 END 
	ELSE 0 END	TVANeexigibil  
	
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(
		SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
		sum(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END)  Baza,
		SUM(ValoareTVA) As ValoareTVA,
		dd.CotaTVA,dd.IDRegimFiscal
		FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
		GROUP BY dd.IDDocument,dd.CotaTVA,dd.IDRegimFiscal
	) docd ON doc.IDDocument = docd.IDDocument
	JOIN RegimFiscal rf ON doc.IDRegimFiscal = rf.IDRegimFiscal  LEFT OUTER JOIN
	(
		SELECT SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<=@PanaLaData AND dp1.Data>=@DeLaData
		GROUP BY ddp1.IDDocument
	) selPlati ON selPlati.IDDocument = docd.IDDocument LEFT OUTER JOIN 	
	(
		SELECT SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<@DeLaData 
		GROUP BY ddp1.IDDocument
	) selPlatiAnterior ON selPlatiAnterior.IDDocument = docd.IDDocument
	WHERE 
	  YEAR(doc.DataDoc) > 2012 AND                     -- dupa 2012 
	  (@cumparari=1 OR tipd.IDTipDocument in (1)) AND
	  (@cumparari=0 OR tipd.IDTipDocument in (0)) AND  -- pentru facturile de cumparare fara bonuri fiscale
	  doc.CodUnitate=@CodUnitate AND                   -- pentru unitatea curenta
	  --LTRIM(RTRIM(part.AtributFiscal))<>'' AND         -- pentru partenerii care au atribut fiscal
	  doc.DataDoc < @DeLaData AND                      -- pentru facturile de dinaintea intervalului selectat
	  (@cumparari=1 OR DATEADD(day,90,doc.DataDoc) > @PanaLaData) AND    -- dar nu mai vechi de 90 de zile
	  (@cumparari=0 OR doc.IDRegimFiscal IN (4,17,18,19,27)) AND         -- cu regim fiscal cu TVA interne
	  (@cumparari=1 OR doc.IDRegimFiscal IN (4,9)) AND         -- cu regim fiscal cu TVA interne
	  --ValoareTotalaSiTVA >= COALESCE(ValPlatCuTVA,0) AND
      EXISTS(SELECT * FROM DocumenteDetaliu dd WHERE dd.IDDocument = docd.IDDocument AND dd.TVAIncasare=1) AND 
                                                    -- pentru facturile care au o linie cu tVA la incasare
      ABS(doc.Total) >  ABS(COALESCE((SELECT SUM(dpd.Valoare) FROM DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata = dp.IDDocumentPlata
	  WHERE dpd.IDDocument = docd.IDDocument AND dp.Data < @DeLaData),0))
												    -- care au ramas neachitate pana in perioada selectata 	

 UNION
    --jurnal vanzari care devin exigibile in perioada curenta
    SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal, 
	docd.*,rf.Denumire AS DenRegim,
	CASE WHEN doc.IDTipDocument IN (0,1) THEN
	Case when docd.IDRegimFiscal IN (4,17,18,19,27,9) THEN 
		CASE WHEN ABS(COALESCE(selPlati.ValPlatCuTVA,0))> ABS(ValoareTotalaSiTVA) THEN ValoareTotalaSiTVA ELSE COALESCE(selPlati.ValPlatCuTVA,0) END 
	ELSE 0 END 
	ELSE selPlati.ValPlatCuTVA END ValPlatCuTVA, 
	 
	Baza - COALESCE(selPlatiAnterior.BazaExigibila,0) BazaExigibila,  
	ValoareTVA - COALESCE(selPlatianterior.TVAExigibil,0)	TVAExigibil, 
	0 BazaNeexigibila,  
	0 TVANeexigibil  
	
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
	(
		SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
		sum(CASE WHEN IDTipDocument in (10,11) THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END)  Baza,
		SUM(ValoareTVA) As ValoareTVA,
		dd.CotaTVA,dd.IDRegimFiscal
		FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
		GROUP BY dd.IDDocument,dd.CotaTVA,dd.IDRegimFiscal
	) docd ON doc.IDDocument = docd.IDDocument
	JOIN RegimFiscal rf ON doc.IDRegimFiscal = rf.IDRegimFiscal  LEFT OUTER JOIN
	(
		SELECT SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<=@PanaLaData AND dp1.Data>=@DeLaData
		GROUP BY ddp1.IDDocument
	) selPlati ON selPlati.IDDocument = docd.IDDocument LEFT OUTER JOIN 	
	(
		SELECT SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<@DeLaData 
		GROUP BY ddp1.IDDocument
	) selPlatiAnterior ON selPlatiAnterior.IDDocument = docd.IDDocument
	WHERE 
	  @cumparari=0 AND								--doar pentru jurnalul de vanzari
	  YEAR(doc.DataDoc) > 2012 AND                     -- dupa 2012 
	  (tipd.IDTipDocument in (1)) AND
	  doc.CodUnitate=@CodUnitate AND                   -- pentru unitatea curenta
	  --LTRIM(RTRIM(part.AtributFiscal))<>'' AND         -- pentru partenerii care au atribut fiscal
	  doc.DataDoc < @DeLaData AND                      -- pentru facturile de dinaintea intervalului selectat
	  (DATEADD(day,90,doc.DataDoc) >= @DeLaData AND DATEADD(day,90,doc.DataDoc) <= @PanaLaData) AND    -- care devin exigibile le 90 de zile
	  (doc.IDRegimFiscal IN (4,9)) AND         -- cu regim fiscal cu TVA interne
	  --ValoareTotalaSiTVA >= COALESCE(ValPlatCuTVA,0) AND
      EXISTS(SELECT * FROM DocumenteDetaliu dd WHERE dd.IDDocument = docd.IDDocument AND dd.TVAIncasare=1) AND 
                                                    -- pentru facturile care au o linie cu tVA la incasare
      ABS(doc.Total) >  ABS(COALESCE((SELECT SUM(dpd.Valoare) FROM DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata = dp.IDDocumentPlata
	  WHERE dpd.IDDocument = docd.IDDocument AND dp.Data < @DeLaData),0))
												    -- care au ramas neachitate pana in perioada selectata 	


	
   ORDER BY DataDoc
END
GO



DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [admin_ApplicationMenu] WHERE [ApplicationId]='8489910A-6599-436A-B3E5-21B6DB74A7BE' AND [IDMeniuAplicatie]=2704
IF @Cate= 0 
begin

SET IDENTITY_INSERT [admin_ApplicationMenu] ON
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2704,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Cheltuieli medicamente pe sectii',2700,1,'Cheltuieli_Medicamente_Sectii',NULL,NULL)    
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(2705,'8489910A-6599-436A-B3E5-21B6DB74A7BE','Cheltuieli materiale sanitare pe sectii',2700,2,'Cheltuieli_Materiale_Sanitare_Sectii',NULL,NULL)    
SET IDENTITY_INSERT [admin_ApplicationMenu] OFF	
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exbug_ListaCheltuieliMedicamenteDepartamente]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_ListaCheltuieliMedicamenteDepartamente]
GO


CREATE PROCEDURE [dbo].[exbug_ListaCheltuieliMedicamenteDepartamente]
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@IDJurnal int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT CASE WHEN d.IDTipDepartament In (16,18) THEN 0
	WHEN d.IDTipDepartament In (2) THEN 1 
	ELSE 2 END ord, d.Nume,td.Nume TipDepartament,
	sum(e.Valoare) Chelt_totale, 
	CASE WHEN Sum(d.NumericField)<>0 THEN Sum(e.Valoare)/Sum(d.NumericField) ELSE 0 END CostMediuPat,
	CASE WHEN Sum(ecp.Internati)<>0 THEN Sum(e.Valoare)/Sum(ecp.Internati) ELSE 0 END CostMediuPacient,
	CASE WHEN Sum(ecp.ZileSpitalizare)<>0 THEN Sum(e.Valoare)/Sum(ecp.ZileSpitalizare) ELSE 0 END CostMediuZiSpital,
	Coalesce(Sum(d.NumericField),0) Nr_paturi,
	Coalesce(Sum(ecp.Internati),0) Nr_internati,
	Coalesce(Sum(ecp.ZileSpitalizare),0) Nr_zile_spitalizare
	FROM Departamente d JOIN TipDepartament td ON td.IDTipDepartament = d.IDTipDepartament
	JOIN ExecutieCheltuieli e ON e.IDDepartament = d.IDDepartament AND d.Activ=1 and e.CodUnitate = @CodUnitate
	JOIN 
	(SELECT e.IDDepartament,e.IDJurnal,e.CodUnitate,Sum(Internati) Internati,Sum(ZileSpitalizare) ZileSpitalizare,Data FROM 
	  ExecutieCheltuieliPacienti e JOIN CaseAsigurariSanatate cas ON e.IDCasaAsigSanatate = cas.IDCasaAsigSanatate 
	  --AND cas.CasaImplicita=1
	 WHERE e.CodUnitate = @CodUnitate
	 GROUP BY e.IDDepartament,e.IDJurnal,e.CodUnitate,data
	) ecp ON e.IDDepartament = ecp.IDDepartament and ecp.CodUnitate=e.CodUnitate and e.Data=ecp.Data
	WHERE d.CodUnitate = @CodUnitate AND e.Data>=@DeLaData and e.Data <=@PanaLaData
	AND e.ArtAl in ('20.04.01')
	GROUP BY d.IDTipDepartament,td.Nume,d.Nume
	having SUM(Valoare)<>0 
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exbug_ListaCheltuieliDepartamente]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_ListaCheltuieliDepartamente]
GO

CREATE PROCEDURE [dbo].[exbug_ListaCheltuieliDepartamente]
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@IDJurnal int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT CASE 
	WHEN d.IDTipDepartament In (16,18) THEN 0 
	WHEN d.IDTipDepartament In (22) THEN 1 
	ELSE 2 END ord, d.Nume,td.Nume TipDepartament,
	sum(e.Valoare) Chelt_totale
	FROM Departamente d JOIN TipDepartament td ON td.IDTipDepartament = d.IDTipDepartament
	JOIN ExecutieCheltuieli e ON e.IDDepartament = d.IDDepartament AND d.Activ=1 and e.CodUnitate = @CodUnitate
	WHERE d.CodUnitate = @CodUnitate AND Data>=@DeLaData AND Data <=@PanaLaData
	GROUP BY d.IDTipDepartament,td.Nume,d.Nume
	having SUM(Valoare)<>0 
	ORDER BY ord,TipDepartament,d.Nume
END



GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exbug_ListaIndicatoriStructuri]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_ListaIndicatoriStructuri]
GO

CREATE PROCEDURE [dbo].[exbug_ListaIndicatoriStructuri]
	-- Add the parameters for the stored procedure here
	@startDate datetime, 
	@endDate datetime,
	@CodUnitate smallint
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @SupTotal decimal(18,2)
	Select @SupTotal = SUM(suprafata) FROM Departamente where IDTipDepartament in (16,18,22) AND IDDepartParinte = 0 AND Activ=1 AND ExecutieCheltuieli=1
	

	SELECT d.Nume NumeDepartament,d.IDTipDepartament,Sum(ecp.Internati) NrPacienti, SUM(ecp.ZileSpitalizare) ZileSpitalizare,
	Sum(d.NumericField) NrPaturi,
	Sum(ec.Valoare+COalesce(ecsub.Valoare_Sub,0)) Cheltuieli_Totale,
	MAX(d.Suprafata) Suprafata, 
	SUM(Valoare_Indirecta)*MAX(Suprafata)/@SupTotal Chelt_indirecte,
	CASE WHEN Sum(ecp.Internati)<>0 THEN Sum(ec.Valoare)/Sum(ecp.Internati) ELSE 0 END CostMediuPacient,
	CASE WHEN Sum(ecp.ZileSpitalizare)<>0 THEN Sum(ec.Valoare)/Sum(ecp.ZileSpitalizare) ELSE 0 END CostMediuZiSpital,
	CASE WHEN Sum(d.NumericField)<>0 THEN Sum(ec.Valoare)/Sum(d.NumericField) ELSE 0 END CostMediuPat,
	CASE WHEN Sum(d.NumericField)<>0 THEN Sum(ZileSpitalizare)/Sum(d.NumericField) ELSE 0 END UtilizarePaturi,
	CASE WHEN Sum(ecp.Internati)<>0 THEN Sum(ZileSpitalizare)/Sum(ecp.Internati) ELSE 0 END DurataSpitalizare,
	COALESCE(Sum(d.NumericField),0) Nr_Paturi,
	COALESCE(Sum(ecp.Internati),0) Internati,
	COALESCE(Sum(ecp.ZileSpitalizare),0) Zile_Spitalizare
	FROM Departamente d 
	left outer JOIN 
	(
	SELECT IDDepartament,CodUnitate,Data,IDJurnal,SUM(Valoare) Valoare FROM ExecutieCheltuieli
	WHERE CodUnitate =@CodUnitate AND 
	YEAR(Data)*100+MONTH(Data) >= YEAR(@startDate)*100+MONTH(@startDate) AND
	YEAR(Data)*100+MONTH(Data) <= YEAR(@endDate)*100+MONTH(@endDate)
	GROUP BY IDDepartament,CodUnitate,Data,IDjurnal
	) ec  ON ec.IDDepartament = d.IDDepartament and ec.CodUnitate = d.CodUnitate 
	left outer JOIN 
	(
	SELECT a1.CodUnitate,Data,a1.IDJurnal,SUM(Valoare) Valoare_Indirecta FROM ExecutieCheltuieli a1 join
	Departamente dd1 ON a1.CodUnitate = dd1.CodUnitate and a1.IDDepartament = dd1.IDDepartament
	WHERE a1.CodUnitate =@CodUnitate AND 
	YEAR(Data)*100+MONTH(Data) >= YEAR(@startDate)*100+MONTH(@startDate) AND
	YEAR(Data)*100+MONTH(Data) <= YEAR(@endDate)*100+MONTH(@endDate)
	and dd1.IDTipDepartament not in (16,18,22)
	GROUP BY a1.CodUnitate,Data,a1.IDjurnal
	) ecind  ON ecind.Data = ec.Data and ecind.CodUnitate = ec.CodUnitate 
	
	LEFT OUTER JOIN 
	(
	 SELECT IDDepartParinte,a1.CodUnitate,Data,a1.IDJurnal,SUM(Valoare) Valoare_Sub FROM ExecutieCheltuieli a1 JOIN Departamente d1 ON a1.CodUnitate = d1.CodUnitate and a1.IDDepartament = d1.IDDepartament
	 WHERE a1.CodUnitate =@CodUnitate AND 
	YEAR(Data)*100+MONTH(Data) >= YEAR(@startDate)*100+MONTH(@startDate) AND
	YEAR(Data)*100+MONTH(Data) <= YEAR(@endDate)*100+MONTH(@endDate)
	 GROUP BY IDDepartParinte,a1.CodUnitate,Data,a1.IDjurnal
	 ) ecsub  ON ecsub.IDDepartParinte = ec.IDDepartament and ecsub.CodUnitate = ec.CodUnitate and ecsub.Data = ec.Data
	 
	 
	LEFT OUTER JOIN 
	(
	 SELECT e.IDDepartament,e.IDJurnal,e.Data,e.CodUnitate,Sum(Internati) Internati,Sum(ZileSpitalizare) ZileSpitalizare FROM 
	  ExecutieCheltuieliPacienti e JOIN CaseAsigurariSanatate cas ON e.IDCasaAsigSanatate = cas.IDCasaAsigSanatate 
	  --AND cas.CasaImplicita=1
	 WHERE e.CodUnitate = @CodUnitate 
	 GROUP BY e.IDDepartament,e.IDJurnal,e.Data,e.CodUnitate
	) ecp ON ec.IDDepartament = ecp.IDDepartament and ecp.CodUnitate=ec.CodUnitate and ecp.Data = ec.Data AND ec.IDJurnal=ecp.IDJurnal
	
	WHERE ec.CodUnitate = @CodUnitate AND 
	YEAR(ec.Data)*100+MONTH(ec.Data) >= YEAR(@startDate)*100+MONTH(@startDate) AND
	YEAR(ec.Data)*100+MONTH(ec.Data) <= YEAR(@endDate)*100+MONTH(@endDate) AND
	d.IDTipDepartament in (16,18,22) 
	AND d.IDDepartParinte = 0 AND d.Activ=1 AND d.ExecutieCheltuieli=1
	GROUP BY d.Nume,d.IDTipDepartament
	ORDER BY d.IDTipDepartament,d.Nume

END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exbug_ListaCheltuieliSanitareDepartamente]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_ListaCheltuieliSanitareDepartamente]
GO



CREATE PROCEDURE [dbo].[exbug_ListaCheltuieliSanitareDepartamente]
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@IDJurnal int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT CASE WHEN d.IDTipDepartament In (16,18) THEN 0
	WHEN d.IDTipDepartament In (2) THEN 1 
	ELSE 2 END ord, d.Nume,td.Nume TipDepartament,
	sum(e.Valoare) Chelt_totale, 
	CASE WHEN Sum(d.NumericField)<>0 THEN Sum(e.Valoare)/Sum(d.NumericField) ELSE 0 END CostMediuPat,
	CASE WHEN Sum(ecp.Internati)<>0 THEN Sum(e.Valoare)/Sum(ecp.Internati) ELSE 0 END CostMediuPacient,
	CASE WHEN Sum(ecp.ZileSpitalizare)<>0 THEN Sum(e.Valoare)/Sum(ecp.ZileSpitalizare) ELSE 0 END CostMediuZiSpital,
	Coalesce(Sum(d.NumericField),0) Nr_paturi,
	Coalesce(Sum(ecp.Internati),0) Nr_internati,
	Coalesce(Sum(ecp.ZileSpitalizare),0) Nr_zile_spitalizare
	FROM Departamente d JOIN TipDepartament td ON td.IDTipDepartament = d.IDTipDepartament
	JOIN ExecutieCheltuieli e ON e.IDDepartament = d.IDDepartament AND d.Activ=1 and e.CodUnitate = @CodUnitate
	JOIN 
	(SELECT e.IDDepartament,e.IDJurnal,e.CodUnitate,Sum(Internati) Internati,Sum(ZileSpitalizare) ZileSpitalizare,Data FROM 
	  ExecutieCheltuieliPacienti e JOIN CaseAsigurariSanatate cas ON e.IDCasaAsigSanatate = cas.IDCasaAsigSanatate 
	  --AND cas.CasaImplicita=1
	 WHERE e.CodUnitate = @CodUnitate
	 GROUP BY e.IDDepartament,e.IDJurnal,e.CodUnitate,data
	) ecp ON e.IDDepartament = ecp.IDDepartament and ecp.CodUnitate=e.CodUnitate and e.Data=ecp.Data
	WHERE d.CodUnitate = @CodUnitate AND e.Data>=@DeLaData and e.Data <=@PanaLaData
	AND e.ArtAl in ('20.04.02')
	GROUP BY d.IDTipDepartament,td.Nume,d.Nume
	having SUM(Valoare)<>0 
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exbug_ListaCheltuieliTotale]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_ListaCheltuieliTotale]
GO

CREATE PROCEDURE [dbo].[exbug_ListaCheltuieliTotale]
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@IDJurnal int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT a.ArtAl,a.Denumire,sum(CASE WHEN d.IDTipDepartament IN(16,18,22) THEN e.Valoare else 0 END) Chelt_directe 
	,sum(CASE WHEN d.IDTipDepartament not IN(16,18,22) THEN e.Valoare else 0 END) Chelt_indirecte 
	,sum(e.Valoare) Chelt_totale
	FROM Articolealiniate a LEFT OUTER JOIN ExecutieCheltuieli e ON dbo.IsContInclus(a.ArtAl,e.ArtAl,a.Terminal)=1 AND a.CodUnitate = e.CodUnitate
	JOIN Departamente d ON e.IDDepartament = d.IDDepartament AND d.Activ=1
	WHERE a.CodUnitate = @CodUnitate AND Data>=@DeLaData and Data <=@PanaLaData
	--AND IDJurnal=@IDJurnal
	GROUP BY a.ArtAl,a.Denumire
	having SUM(Valoare)<>0 
	
	UNION	
	SELECT '1' ArtAl,'CHELTUIELI TOTALE' denumire,sum(CASE WHEN d.IDTipDepartament IN(16,18,22) THEN e.Valoare else 0 END) Chelt_directe 
	,sum(CASE WHEN d.IDTipDepartament not IN(16,18,22) THEN e.Valoare else 0 END) Chelt_indirecte 
	,sum(e.Valoare) Chelt_totale
	FROM Articolealiniate a LEFT OUTER JOIN ExecutieCheltuieli e ON e.ArtAl=a.ArtAl AND a.CodUnitate = e.CodUnitate
	JOIN Departamente d ON e.IDDepartament = d.IDDepartament AND d.Activ=1
	WHERE a.CodUnitate = @CodUnitate AND Data>=@DeLaData and Data <=@PanaLaData 
	--AND IDJurnal=@IDJurnal	
	Order by a.ArtAl
END


GO


DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [admin_ApplicationMenu] WHERE [ApplicationId]='E03B2883-43E3-41B7-A41E-A620126D1BE0' AND [IDMeniuAplicatie]=1061
IF @Cate= 0 
begin

SET IDENTITY_INSERT [admin_ApplicationMenu] ON
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(1061,'E03B2883-43E3-41B7-A41E-A620126D1BE0','Garzi',NULL,40,'Resuma_Garzi',NULL,NULL)    
SET IDENTITY_INSERT [admin_ApplicationMenu] OFF	
END
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AngajatiGarzi]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AngajatiGarzi](
	[IDGarda] [int] IDENTITY(1,1) NOT NULL,
	[CodUnitate] [smallint] NOT NULL,
	[Luna] [smallint] NOT NULL,
	[Anul] [smallint] NOT NULL,
	[IDPersoana] [int] NOT NULL,
	[IDAngajatSalarizare] [int] NOT NULL,
	[StartGarda] [datetime] NOT NULL,
	[EndGarda] [datetime] NOT NULL,
	[IDAngajatInlocuit] [int] NULL,
 CONSTRAINT [PK_AngajatiGarzi] PRIMARY KEY CLUSTERED ([IDGarda] ASC),
 CONSTRAINT [FK_AngajatiGarzi_AngajatiSalarizare] FOREIGN KEY([IDAngajatSalarizare]) REFERENCES [dbo].[AngajatiSalarizare] ([IDAngajatSalarizare]),
 CONSTRAINT [FK_AngajatiGarzi_AngajatiSalarizare1] FOREIGN KEY([IDAngajatInlocuit]) REFERENCES [dbo].[AngajatiSalarizare] ([IDAngajatSalarizare]),
 CONSTRAINT [FK_AngajatiGarzi_Persoane] FOREIGN KEY([IDPersoana]) REFERENCES [dbo].[Persoane] ([IdPersoana]),
 CONSTRAINT [FK_AngajatiGarzi_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
)
END
GO



IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ConcediiMedicale' AND COLUMN_NAME = 'CodBoalaContagioasa')
BEGIN
	ALTER TABLE dbo.ConcediiMedicale ADD CodBoalaContagioasa varchar(20) NULL
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ConcediiMedicale' AND COLUMN_NAME = 'ZileBazaCM')
BEGIN
	ALTER TABLE dbo.ConcediiMedicale ADD ZileBazaCM int NOT NULL DEFAULT(0)
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ConcediiMedicale' AND COLUMN_NAME = 'CNPCopil')
BEGIN
	ALTER TABLE dbo.ConcediiMedicale ADD CNPCopil varchar(13) NULL
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ConcediiMedicale' AND COLUMN_NAME = 'LocPrescriere')
BEGIN
	ALTER TABLE dbo.ConcediiMedicale ADD LocPrescriere varchar(2) NULL
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ConcediiMedicale' AND COLUMN_NAME = 'FaraPlataIndemnizatie')
BEGIN
	ALTER TABLE dbo.ConcediiMedicale ADD FaraPlataIndemnizatie bit NULL
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ConcediiMedicale' AND COLUMN_NAME = 'AvizITM_DSP')
BEGIN
	ALTER TABLE dbo.ConcediiMedicale ADD AvizITM_DSP bit NULL
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ConcediiMedicale' AND COLUMN_NAME = 'BazaCalculataIncorect')
BEGIN
	ALTER TABLE dbo.ConcediiMedicale ADD BazaCalculataIncorect bit NULL
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TipPrezenta' AND COLUMN_NAME = 'CodUnitate')
BEGIN
	ALTER TABLE dbo.TipPrezenta ADD CodUnitate smallint NOT NULL DEFAULT 0

	ALTER TABLE dbo.TipPrezenta ADD CONSTRAINT
		FK_TipPrezenta_Unitati FOREIGN KEY (CodUnitate) REFERENCES dbo.Unitati (CodUnitate) ON UPDATE  NO ACTION 
		 ON DELETE  NO ACTION 
	
END
GO

IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TipPrezenta]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TipPrezenta](
	[CodPrezenta] [varchar](15) NULL,
	[CodListare] [varchar](15) NULL,
	[Ordonare] [tinyint] NULL,
	[TimpBaza] [char](1) NULL,
	[IdTipPrezenta] [smallint] IDENTITY(1,1) NOT NULL,
	[Descriere] [varchar](100) NULL,
	[DataDefinire] [datetime] NULL,
	[Selectat] [bit] NOT NULL,
	[SeListeaza] [bit] NOT NULL,
	[IdOpContabila] [int] NOT NULL,
	[Tip] [char](1) NOT NULL DEFAULT ('P'),
	[ProcentBaza] [decimal](5, 2) NOT NULL DEFAULT ((0)),
	[InclusCAS] [bit] NOT NULL DEFAULT ((0)),
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
 CONSTRAINT [PK_TipPrezenta] PRIMARY KEY CLUSTERED ([IdTipPrezenta] ASC),
 CONSTRAINT [FK_TipPrezenta_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate]),
 CONSTRAINT [CK_TipPrezenta_TimpBaza] CHECK  (([TimpBaza]='O' OR [TimpBaza]='Z' OR [TimpBaza]='V')),
 CONSTRAINT [CK_TipPrezenta_Tip] CHECK  (([Tip]='P' OR [Tip]='O' OR [Tip]='N' OR [Tip]='X'))
)
END 
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ResUmaSetup' AND COLUMN_NAME = 'SituatieMilitara')
BEGIN
	ALTER TABLE dbo.ResUmaSetup ADD SituatieMilitara bit NOT NULL DEFAULT(0)
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[arhAngajatiSalarizare]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[arhAngajatiSalarizare](
	[IDAngajatSalarizare] [int] IDENTITY(1,1) NOT NULL,
	[IdPersoana] [int] NOT NULL,
	[DeLaData] [datetime] NOT NULL DEFAULT (getdate()),
	[Salar] [int] NOT NULL,
	[IDDepartament] [int] NOT NULL,
	[IdTipGrad] [int] NOT NULL DEFAULT ((0)),
	[OreNorma] [smallint] NOT NULL DEFAULT ((0)),
	[FractiuneNorma] [decimal](5, 2) NOT NULL DEFAULT ((1)),
	[PanaLaData] [datetime] NULL,
	[NormaBaza] [bit] NOT NULL DEFAULT ((0)) ,
	[ServiciiPeChitanta] [bit] NULL,
	[CodUnitate] [smallint] NOT NULL DEFAULT ((0)),
	[Clasa] [smallint] NOT NULL DEFAULT ((0)),
	[Grupa] [varchar](20) NULL,
	[IDJurnal] [int] NULL,
	[SalarFunctie] [decimal](18, 2) NOT NULL DEFAULT ((0)),
	[Marca] [int] NULL,
	[NrContract] [varchar](20) NULL,
	[DataContract] [datetime] NULL,
	[TipContract] [int] NULL,
	[PerioadaDeterminata] [bit] NULL,
	[Observatii] [varchar](250) NULL,
	[DurataContract] [float] NULL,
	[Luna] smallint NOT NULL,
	[Anul] smallint NOT NULL,
 CONSTRAINT [PK_arhAngajatiSalarizare] PRIMARY KEY CLUSTERED ([IDAngajatSalarizare] ASC),
 CONSTRAINT [FK_arhAngajatiSalarizare_DEPARTAMENTE] FOREIGN KEY([IDDepartament]) REFERENCES [dbo].[Departamente] ([IDDepartament]),
 CONSTRAINT [FK_arhAngajatiSalarizare_Jurnale] FOREIGN KEY([IDJurnal]) REFERENCES [dbo].[Jurnale] ([IDJurnal]),
 CONSTRAINT [FK_arhAngajatiSalarizare_Persoane] FOREIGN KEY([IdPersoana]) REFERENCES [dbo].[Persoane] ([IdPersoana]),
 CONSTRAINT [FK_arhAngajatiSalarizare_TipContracte] FOREIGN KEY([TipContract])REFERENCES [dbo].[TipContracte] ([TipContract]),
 CONSTRAINT [FK_arhAngajatiSalarizare_Unitati] FOREIGN KEY([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
) 

END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[arhAngajatiSporuri]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[arhAngajatiSporuri](
	[IdTipSpor] [int] NOT NULL,
	[IdPersoana] [int] NOT NULL,
	[DeLaData] [datetime] NOT NULL,
	[IdPersoanaSpor] [int] IDENTITY(1,1) NOT NULL,
	[PanaLaData] [datetime] NULL,
	[Valoare] [decimal](18, 2) NOT NULL  DEFAULT ((0)),
	[Procent] [decimal](6, 2) NOT NULL DEFAULT ((0)),
	[ValoarePreaviz] [decimal](18, 2) NOT NULL DEFAULT ((0)),
	[IDAngajatSalarizare] [int] NULL,
	[Luna] smallint NOT NULL,
	[Anul] smallint NOT NULL,
 CONSTRAINT [PK_arhAngajatiSporuri] PRIMARY KEY CLUSTERED ([IdPersoanaSpor] ASC),
 CONSTRAINT [FK_arhAngajatiSporuri_arhAngajatiSalarizare] FOREIGN KEY([IDAngajatSalarizare]) REFERENCES [dbo].[arhAngajatiSalarizare] ([IDAngajatSalarizare]),
 CONSTRAINT [FK_arhAngajatiSporuri_Persoane] FOREIGN KEY([IdPersoana]) REFERENCES [dbo].[Persoane] ([IdPersoana])
)
END
GO





DECLARE @cate int
SELECT @Cate = COUNT(*) FROM [admin_ApplicationMenu] WHERE [ApplicationId]='E03B2883-43E3-41B7-A41E-A620126D1BE0' AND [IDMeniuAplicatie]=1062
IF @Cate= 0 
begin

SET IDENTITY_INSERT [admin_ApplicationMenu] ON
INSERT INTO [admin_ApplicationMenu] ([IDMeniuAplicatie],[ApplicationId],[Name],[IdMeniuParinte],[Ordine],[HandlerName],[Tag],[Vizibil])VALUES(1062,'E03B2883-43E3-41B7-A41E-A620126D1BE0','Consultare Arhiva',NULL,100,'ConsultareArhiva',NULL,NULL)
SET IDENTITY_INSERT [admin_ApplicationMenu] OFF	
END
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Arhive' AND COLUMN_NAME = 'Luna')
BEGIN
	ALTER TABLE dbo.Arhive ADD Luna smallint NOT NULL DEFAULT(0)
END
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Arhive' AND COLUMN_NAME = 'ResUma')
BEGIN
	ALTER TABLE dbo.Arhive ADD ResUma bit NOT NULL DEFAULT(0)
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[resuma_ImportPersSal]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[resuma_ImportPersSal]
GO

CREATE PROCEDURE [dbo].[resuma_ImportPersSal]
	@CodUnitate smallint
AS
BEGIN
	DECLARE @Cod_Unit decimal(18,0)
	SET @Cod_Unit = 281479271679015 
	
	SET NOCOUNT ON;
	DELETE FROM Arhive WHERE CodUnitate=@CodUnitate
	INSERT INTO [Arhive]
           ([Denumire]
           ,[CodUnitate]
           ,[DataInceput]
           ,[DataSfarsit]
           ,[Anul]
           ,[BalCon]
           ,[Fares]
           ,[Luna]
           ,[ResUma])
     SELECT 
           CAST(Anul AS Varchar(4)) + ',' +  CAst(Luna as varchar(2))
           ,@CodUnitate
           ,CAST(CAST(Anul AS Varchar(4))  + '-' +  CAst(Luna as varchar(2))  + '-01' As Datetime)
           ,DATEADD(day,-1,DATEADD(month,1,CAST(CAST( Anul AS Varchar(4))  + '-' +  CAst(Luna as varchar(2))  + '-01' As Datetime)))
           ,Anul
           ,0
           ,0
           ,Luna
           ,1 
           FROM PersSal.dbo.pArhCfgLuna  pArh WHERE
           Cod_Unit = @Cod_Unit




	/*DELETE FROM [Import_Persoane]
    INSERT INTO [Import_Persoane]
           ([NumePrenume]
           ,[Nume_Anterior]
           ,[Cnp]
           ,[Sex]
           ,[DataNasterii]
           ,[IDLocNastere]
           ,[TipAI]
           ,[SeriaAI]
           ,[NumarAI]
           ,[DataElibAI]
           ,[EmitentAI]
           ,[NumeTata]
           ,[NumeMama]
           ,[StradaAdr]
           ,[IDLocalitateAdr]
           ,[NrAdr]
           ,[BlocAdr]
           ,[EtajAdr]
           ,[ScaraAdr]
           ,[ApartamentAdr]
           ,[GradInvaliditate]
           ,[TelefonFix]
           ,[TelefonMobil]
           ,[Observatii]
           ,[NumarCM]
           ,[SerieCM]
           ,[CodUnitate]
           ,[NrFileCM]
           ,[NrCutieCM]
           ,[Marca]
           ,[TelefonInterior]
           ,[IdCasaAsigSanatate]
           ,[IdTipStudii]
           ,[DataVechimeMunca]
           ,[PozCutieCM]
           ,[PlatesteCAS]
           ,[PlatesteCASS]
           ,[PlatesteSomaj]
           ,[PlatesteImpozit]
           ,[AreDeducere]
           ,[DataPlecarii]
           ,[MotivIncetareContract]
           ,[Operatie]
           ,[Selectat])
     SELECT  LTRIM(RTRIM([Nume])) + ' ' + LTRIM(RTRIM([Prenume]))  as Nume_Prenume,
     LTRIM(RTRIM(Anterior)),pp.[Cnp],pp.Sex,[DataNast],
     COALESCE(
     (SELECT TOP 1 IDLocalitate FROM Localitati WHERE (Nume like '%' + LN_Loc + '%') OR (Nume like '%' + LN_Sat + '%')),0) IDLocalitate,
     AI_Tip, AI_Ser, AI_Nr,COALESCE(AI_DatEl,GETDATE()) [DataElibAI] ,AI_Elib,
     '' AS NumeMama, '' AS NumeTata, Adr_Str,
     COALESCE((SELECT TOP 1 IDLocalitate FROM Localitati WHERE (Nume like '%' + Adr_Loc + '%') OR (Nume like '%' + Adr_Sat + '%')),0) IDLocalitateAdr,
     Adr_Nr,Adr_Bloc,Adr_Et, Adr_Sc, Adr_Ap,
     Handic as GradInvaliditate, Adr_Tel,Adr_Tel, Mentiuni AS Observatii,
     CMNr,CMSer,@CodUnitate,0 AS NrFile, 0 AS NrCutie,pp.Marca,  '' as Telefoninterior, CodCasaSan,
     CodStudii,COALESCE(COALESCE(DataPrAnga,DataUnit),GETDATE()) as [DataVechimeMunca], 0 AS PozCutieCM,  
     1 AS PlatesteCAS,ScutAsSA=1, ScutSomA,ScutImpA,0 as arededucere,DataPlec,
     artPlec + '-' + litPlec as MotivIncetare,0,1 AS Selectat  
     FROM PersSal.dbo.pPerson pp LEFT OUTER JOIN [Persoane] pr ON pp.CNP = pr.Cnp AND pr.CodUnitate = @CodUnitate
     JOIN PersSal.dbo.pPersSal pe ON pp.Marca=pe.Marca
     WHERE  pr.IdPersoana is null*/
	
END

GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AngajatiSalarizare' AND COLUMN_NAME = 'VechimeSporAni')
BEGIN
	ALTER TABLE dbo.AngajatiSalarizare ADD VechimeSporAni smallint NOT NULL DEFAULT(0)
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AngajatiSalarizare' AND COLUMN_NAME = 'VechimeSporLuni')
BEGIN
	ALTER TABLE dbo.AngajatiSalarizare ADD VechimeSporLuni decimal(6,3) NOT NULL DEFAULT(0)
END
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[UpdateInsert]'))
DROP TRIGGER [dbo].[UpdateInsert]
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[UpdateInsertAngajatSporuri]'))
DROP TRIGGER [dbo].[UpdateInsertAngajatSporuri]
GO




IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AngajatiSporuri' AND COLUMN_NAME = 'Luna')
BEGIN
	ALTER TABLE dbo.AngajatiSporuri ADD Luna smallint NOT NULL DEFAULT(0)
END
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AngajatiSporuri' AND COLUMN_NAME = 'Anul')
BEGIN
	ALTER TABLE dbo.AngajatiSporuri ADD Anul smallint NOT NULL DEFAULT(0)
END
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AngajatiSporuri' AND COLUMN_NAME = 'ValoareCalculata')
BEGIN
	ALTER TABLE dbo.AngajatiSporuri DROP COLUMN ValoareCalculata
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[resuma_CalculValoareSpor]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[resuma_CalculValoareSpor]
GO


CREATE FUNCTION [dbo].[resuma_CalculValoareSpor]
(
	-- Add the parameters for the function here
	@IdPersoanaSpor int
)
RETURNS decimal(18,2)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar decimal(18,2)
    DECLARE @BazaCalculSpor varchar(2)
    DECLARE @Format varchar(1)
    DECLARE @IDOcupatie int
    DECLARE @ProcentG decimal(6,2)
    DECLARE @Procent decimal(6,2)
    DECLARE @Valoare decimal(18,2)
    DECLARE @IDAngajatSalarizare int
    DECLARE @SalarCalculSpor decimal(18,2)
    DECLARE @IDTipSpor int
    DECLARE @Vechime decimal(18,3)
	DECLARE @dataAplicare datetime
	
	SELECT TOP 1 @dataAplicare = CAST(CAST(AnulCurent as varchar(4)) + '-' + CAST(LunaCurenta as varchar(2)) + '-01'  AS datetime) FROM ResUmaSetup a
	JOIN AngajatiSporuri d ON d.CodUnitate=a.CodUnitate
	WHERE d.IdPersoanaSpor = @IdPersoanaSpor
	
	SET @ResultVar=0
	SET @Valoare = 0
	-- Insert statements for procedure here
	SELECT @Procent = b.Procent,@BazaCalculSpor = BazaCalculSpor,@Format=Format,@Valoare=b.Valoare,@IDAngajatSalarizare = b.IDAngajatSalarizare
	,@IDTipSpor = a.IDTipSpor
	FROM [TipSpor] a 
	JOIN [AngajatiSporuri] b on a.IDTipSpor = b.IdTipSpor 
	WHERE Activ=1 AND b.IdPersoanaSpor = @IdPersoanaSpor
	AND @dataAplicare>=b.DeLaData AND
	(b.PanaLaData is null or b.PanaLaData>=@dataAplicare)
	
	
	
	
	IF @Format is null
		return @ResultVar
	
	IF @Format ='S' 
	BEGIN
		SET @ResultVar=@Valoare
		return @ResultVar
	END
	
	SELECT  @SalarCalculSpor = a.Salar,@Vechime = a.VechimeSporAni*12 + a.VechimeSporLuni FROM [AngajatiSalarizare] a WHERE a.IDAngajatSalarizare = @IDAngajatSalarizare
	
	IF @Format ='P' 
	BEGIN
		IF @BazaCalculSpor='SI'
			SET @ResultVar=ROUND(@Procent/100.00*@SalarCalculSpor,0)
		
		return @ResultVar
	END
	
	IF @Format ='G' 
	BEGIN
		SELECT @ProcentG=Valoare FROM  Grile gr
		WHERE gr.IDTipSpor = @IDTipSpor AND 
		@Vechime >= gr.Minim*12 AND @vechime < gr.Maxim*12
			
		IF @BazaCalculSpor='SI'	
			SET @ResultVar=ROUND(@ProcentG/100.00*@SalarCalculSpor,0)
		
		return @ResultVar
	END
	
	RETURN @ResultVar

END

GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AngajatiSporuri' AND COLUMN_NAME = 'ValoareCalculata')
BEGIN
	ALTER TABLE dbo.AngajatiSporuri ADD ValoareCalculata AS dbo.[resuma_CalculValoareSpor](IDPersoanaSpor)
END
GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AngajatiSalarizare' AND COLUMN_NAME = 'CodUnitate')
BEGIN
	ALTER TABLE dbo.AngajatiSalarizare ADD CodUnitate smallint NOT NULL DEFAULT 0

	ALTER TABLE dbo.AngajatiSalarizare ADD CONSTRAINT
		FK_AngajatiSalarizare_Unitati FOREIGN KEY (CodUnitate) REFERENCES dbo.Unitati (CodUnitate) ON UPDATE  NO ACTION 
		 ON DELETE  NO ACTION 
	
END
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AngajatiSporuri' AND COLUMN_NAME = 'CodUnitate')
BEGIN
	ALTER TABLE dbo.AngajatiSporuri ADD CodUnitate smallint NOT NULL DEFAULT 0

	ALTER TABLE dbo.AngajatiSporuri ADD CONSTRAINT
		FK_AngajatiSporuri_Unitati FOREIGN KEY (CodUnitate) REFERENCES dbo.Unitati (CodUnitate) ON UPDATE  NO ACTION 
		 ON DELETE  NO ACTION 
	
END
GO


IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AngajatiSporuri' AND COLUMN_NAME = 'FormatSpor')
BEGIN
	ALTER TABLE dbo.AngajatiSporuri DROP COLUMN FormatSpor
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[resuma_GetSporFormat]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[resuma_GetSporFormat]
GO


CREATE FUNCTION [dbo].[resuma_GetSporFormat]
(
	@IDTipSpor int
)
RETURNS VARCHAR(2)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar varchar(2)

	-- Add the T-SQL statements to compute the return value here
	SELECT @ResultVar=Format From TipSpor WHERE IDTipSpor =@IDTipSpor

	-- Return the result of the function
	RETURN @ResultVar

END

GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AngajatiSporuri' AND COLUMN_NAME = 'FormatSpor')
BEGIN
	ALTER TABLE dbo.AngajatiSporuri ADD FormatSpor AS dbo.[resuma_GetSporFormat](IDTipSpor)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[resuma_GetProcentGrila]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[resuma_GetProcentGrila]
GO

CREATE PROCEDURE [dbo].[resuma_GetProcentGrila]
	-- Add the parameters for the stored procedure here
	@IDAngajatSalarizare int,
	@IDTipSpor int,
	@Procent decimal(6,2) out
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @Vechime decimal(18,3)

	SET @Procent = 0
    -- Insert statements for procedure here
	SELECT  @Vechime = a.VechimeSporAni*12 + a.VechimeSporLuni FROM [AngajatiSalarizare] a WHERE a.IDAngajatSalarizare = @IDAngajatSalarizare
	
	SELECT @Procent=Valoare FROM  Grile gr
		WHERE gr.IDTipSpor = @IDTipSpor AND 
		@Vechime >= gr.Minim*12 AND @vechime < gr.Maxim*12
END

GO


IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AngajatiSporuri' AND COLUMN_NAME = 'OrdineSpor')
BEGIN
	ALTER TABLE dbo.AngajatiSporuri DROP COLUMN OrdineSpor
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[resuma_GetSporOrder]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[resuma_GetSporOrder]
GO


CREATE FUNCTION [dbo].[resuma_GetSporOrder]
(
	@IDTipSpor int
)
RETURNS int
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar int

	-- Add the T-SQL statements to compute the return value here
	SELECT @ResultVar=Ordonare From TipSpor WHERE IDTipSpor =@IDTipSpor

	-- Return the result of the function
	RETURN @ResultVar

END

GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AngajatiSporuri' AND COLUMN_NAME = 'OrdineSpor')
BEGIN
	ALTER TABLE dbo.AngajatiSporuri ADD OrdineSpor AS dbo.[resuma_GetSporOrder](IDTipSpor)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exbug_ListaCheltuieliTotaleDepartamente]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exbug_ListaCheltuieliTotaleDepartamente]
GO


CREATE PROCEDURE [dbo].[exbug_ListaCheltuieliTotaleDepartamente]
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime,
	@IDJurnal int,
	@totale bit
AS
BEGIN
	SET NOCOUNT ON;

	SELECT CASE WHEN d.IDTipDepartament In (16,18) THEN 0
	WHEN d.IDTipDepartament In (2) THEN 1 
	ELSE 2 END ord, d.Nume,td.Nume TipDepartament,
	sum(e.Valoare)  Chelt_total,
	sum(Case when e.ArtAl like '10.%' then e.Valoare else 0 end)  Chelt_de_Personal, 
	sum(Case when e.ArtAl like '20.%' then e.Valoare else 0 end)  Chelt_Materiale, 
	sum(Case when e.ArtAl like '20.03%' then e.Valoare else 0 end)  Chelt_Hrana, 
	sum(Case when e.ArtAl like '20.04.01%' then e.Valoare else 0 end)  Chelt_Medicamente, 
	sum(Case when e.ArtAl like '20.04.02%' then e.Valoare else 0 end)  Chelt_Mat_San, 
	sum(Case when e.ArtAl like '71.%' then e.Valoare else 0 end)  Chelt_Active, 
	CASE WHEN Sum(d.NumericField)<>0 THEN Sum(e.Valoare)/Sum(d.NumericField) ELSE 0 END CostMediuPat,
	CASE WHEN Sum(ecp.Internati)<>0 THEN Sum(e.Valoare)/Sum(ecp.Internati) ELSE 0 END CostMediuPacient,
	CASE WHEN Sum(ecp.ZileSpitalizare)<>0 THEN Sum(e.Valoare)/Sum(ecp.ZileSpitalizare) ELSE 0 END CostMediuZiSpital,
	Coalesce(Sum(d.NumericField),0) Nr_paturi,
	Coalesce(Sum(ecp.Internati),0) Nr_internati,
	Coalesce(Sum(ecp.ZileSpitalizare),0) Nr_zile_spitalizare
	FROM Departamente d JOIN TipDepartament td ON td.IDTipDepartament = d.IDTipDepartament
	LEFT OUTER JOIN ExecutieCheltuieli e ON e.IDDepartament = d.IDDepartament AND d.Activ=1 and e.CodUnitate = @CodUnitate
	LEFT OUTER JOIN 
	(SELECT e.IDDepartament,e.IDJurnal,e.CodUnitate,Sum(Internati) Internati,Sum(ZileSpitalizare) ZileSpitalizare,Data FROM 
	  ExecutieCheltuieliPacienti e JOIN CaseAsigurariSanatate cas ON e.IDCasaAsigSanatate = cas.IDCasaAsigSanatate 
	  --AND cas.CasaImplicita=1
	 WHERE e.CodUnitate = @CodUnitate
	 GROUP BY e.IDDepartament,e.IDJurnal,e.CodUnitate,data
	) ecp ON e.IDDepartament = ecp.IDDepartament and ecp.CodUnitate=e.CodUnitate and e.Data=ecp.Data
	WHERE d.CodUnitate = @CodUnitate AND e.Data>=@DeLaData and e.Data <=@PanaLaData and d.ExecutieCheltuieli=1 AND
	(@totale=1 OR d.IDTipDepartament in (16,18,22)) 
	--AND e.ArtAl in ('20.04.02')
	GROUP BY d.IDTipDepartament,td.Nume,d.Nume
	having SUM(Valoare)<>0 
END


GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GenNotaContabila]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GenNotaContabila]
GO

CREATE PROCEDURE [dbo].[balcon_GenNotaContabila]
	@idLista int,
	@cont TipCont,
	@CodUnitate smallint,
	@DeLaData datetime,
	@PanaLaData datetime,
	@An smallint,
	@iOrder smallint,
	@contDebit TipCont,
	@contCredit TipCont
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @strContStart as TipCont,@strContEnd as TipCont
	DECLARE @tipb as varchar(100)
	DECLARE @oct as varchar(100)
	DECLARE @jurnal int
	SET @strContStart=''
	SET @strContEnd=''
	SET @jurnal= -1
	SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='JRN' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
	if @tipb<>''
		SELECT @jurnal = IDJurnal FROM [Jurnale] WHERE Codificare=@tipb
	
	SET @oct=''
	SELECT @oct=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='OCT' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
	

    SELECT @strContStart=LTRIM(RTRIM(Valoare)) FROM [ParamListe] WHERE IDLista=@idLista AND Cod='DLC' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
    SELECT @strContEnd=LTRIM(RTRIM(Valoare)) FROM [ParamListe] WHERE IDLista=@idLista AND Cod='PLC' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate

	if @An<=0
	SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.ContDebit, a.ContCredit, 
           b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
           b.TipFormula, c.Categorie, c.Tip, a.CodUnitate,a.IDDocument
	FROM  [Rulaje] a 
	      INNER JOIN  [RulajeDoc] b ON a.IDDocument = b.IDDocument 
	      INNER JOIN  [Conturi] c ON a.ContDebit = c.Cont AND c.CodUnitate = a.CodUnitate
		  WHERE (a.CodUnitate = @CodUnitate) 
				 AND ((UPPER(@oct)='DEBIT' AND (@strContStart='' OR a.ContDebit>=@strContStart) AND (@strContEnd='' OR a.ContDebit<=@strContEnd)) 
				 OR (UPPER(@oct)='CREDIT' AND (@strContStart='' OR a.ContCredit>=@strContStart) AND (@strContEnd='' OR a.ContCredit<=@strContEnd))
				 OR (UPPER(@oct)=''))
				 AND (@cont='' OR (a.ContDebit = @cont OR a.ContCredit =@cont))
				 AND (b.DataDoc >= @DeLaData AND b.DataDoc<= @PanaLaData)
				 AND (@jurnal=-1 OR b.IDJurnal=@jurnal)
				 AND (@contDebit='' OR (a.ContDebit = @contDebit))
				 AND (@contCredit='' OR (a.ContCredit =@contCredit))

	ORDER BY CASE WHEN @iOrder = 1 THEN ContDebit 
	              WHEN @iOrder = 2 THEN ContCredit 
	              ELSE CAST(b.DataDoc AS VARCHAR(50)) END
	ELSE
	SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.ContDebit, a.ContCredit, 
           b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
           b.TipFormula, c.Categorie, c.Tip, a.CodUnitate,a.IDDocument
	FROM  [arhRulaje] a 
	      INNER JOIN  [arhRulajeDoc] b ON a.IDDocument = b.IDDocument and a.An=@An
	      INNER JOIN  [arhConturi] c ON a.ContDebit = c.Cont AND c.CodUnitate = a.CodUnitate and a.An =c.An
		  WHERE (a.CodUnitate = @CodUnitate)
				 AND ((UPPER(@oct)='DEBIT' AND (@strContStart='' OR a.ContDebit>=@strContStart) AND (@strContEnd='' OR a.ContDebit<=@strContEnd)) 
				 OR (UPPER(@oct)<>'DEBIT' AND (@strContStart='' OR a.ContCredit>=@strContStart) AND (@strContEnd='' OR a.ContCredit<=@strContEnd))
				 OR (UPPER(@oct)=''))
				 AND (@cont='' OR (a.ContDebit = @cont OR a.ContCredit =@cont))
				 AND (b.DataDoc >= @DeLaData AND b.DataDoc<= @PanaLaData)
				 AND (@jurnal=-1 OR b.IDJurnal=@jurnal)
				 AND (@contDebit='' OR (a.ContDebit = @contDebit))
				 AND (@contCredit='' OR (a.ContCredit =@contCredit))

	ORDER BY CASE WHEN @iOrder = 1 THEN ContDebit 
	              WHEN @iOrder = 2 THEN ContCredit 
	              ELSE CAST(b.DataDoc AS VARCHAR(50)) END


END

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CalcDisponibilOrdRows]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CalcDisponibilOrdRows]
GO

CREATE PROCEDURE [dbo].[CalcDisponibilOrdRows]  
	@IDClasificare int, 
	@Analitic TipCont,
	@PanaLa DateTime,
	@CodDoc int,
	@CodUnitate smallint
AS 
begin
	DECLARE @strEroare AS varchar(200)

	DECLARE @cont TipCont
	DECLARE @cont2 TipCont

	SET @cont ='8062.'+@Analitic
	SET @cont2 ='7701.'+@Analitic
	SELECT NumarDoc, DataDoc, coalesce(r.ValoareLei,0) ValoareLeiDebit, 0  ValoareLeiCredit, ContDebit Cont FROM Rulaje r
	JOIN RulajeDoc d ON r.IDDocument = d.IDDocument AND IDJurnal = @IDClasificare AND d.[CodUnitate] = @CodUnitate AND (d.DataDoc<=@PanaLa)  
	WHERE  ContDebit=@cont
    UNION		
	SELECT NumarDoc, DataDoc, 0 ValoareLeiDebit, coalesce(r.ValoareLei,0) ValoareLeiCredit, ContCredit Cont FROM Rulaje r
	JOIN RulajeDoc d ON r.IDDocument = d.IDDocument AND
		IDJurnal = @IDClasificare  AND d.[CodUnitate] = @CodUnitate AND (d.DataDoc<=@PanaLa) 
	WHERE ContCredit=@cont
	--@totDebitPla	
	UNION
	SELECT NumarDoc, DataDoc,coalesce(r.ValoareLei,0) ValoareLeiDebit, 0 ValoareLeiCredit, ContDebit Cont FROM [Rulaje] r 
	JOIN RulajeDoc d ON  r.IDDocument = d.IDDocument AND
		IDJurnal = @IDClasificare AND d.[CodUnitate] = @CodUnitate AND DataDoc<@PanaLa
	WHERE ContDebit= @cont2 AND r.TipDocJust<>'IC'
    --@totCreditPla
    UNION
	SELECT NumarDoc, DataDoc,0 ValoareLeiDebit, coalesce(r.ValoareLei,0) ValoareLeiCredit, ContCredit Cont FROM [Rulaje] r 
	JOIN RulajeDoc d ON r.IDDocument = d.IDDocument AND
		 IDJurnal = @IDClasificare AND d.[CodUnitate] = @CodUnitate AND DataDoc<@PanaLa
    WHERE ContCredit=@cont2 AND r.TipDocJust<>'IC'
    --@totOrdonantat
    UNION
	SELECT NrDoc, DataDoc,0 ValoareLeiDebit, coalesce(ValoareLei,0) ValoareLeiCredit, 'ORDONANTARE' Cont FROM [Ordonantari] WHERE  (DataDoc<@PanaLa 
			OR (DataDoc=@PanaLa  AND IDOrdonantare<@CodDoc)) AND ArtAl Like @Analitic+'%'
			AND IDJurnal=@IDClasificare AND [CodUnitate] = @CodUnitate AND Achitat=0	
	ORDER BY DataDoc
	--SET @s944 = @totDebit + @totDebitPla - @totCredit - @totCreditPla - @totOrdonantat
    --PRINT (@totOrdonantat)


	 GOTO Gata
Eroare:
	 RAISERROR (@strEroare,16,-1)
Gata:
end

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CalcDisponibilOrd]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CalcDisponibilOrd]
GO


CREATE PROCEDURE [dbo].[CalcDisponibilOrd]  @IDClasificare int, @Analitic TipCont,@PanaLa DateTime,@CodDoc int,@CodUnitate smallint,
	@s940 DECIMAL(20,2) OUT, @s944 DECIMAL(20,2) OUT, @s950 DECIMAL(20,2) OUT, @s960 DECIMAL(20,2) OUT AS 
begin
	DECLARE @strEroare AS varchar(200)
	DECLARE @totDebit AS DECIMAL(20,2)
	DECLARE @totCredit AS DECIMAL(20,2)
	DECLARE @totDebitPla AS DECIMAL(20,2)
	DECLARE @totCreditPla AS DECIMAL(20,2)
	DECLARE @totOrdonantat AS DECIMAL(20,2)

	DECLARE @cont TipCont


	SET @cont ='8060.'+@Analitic
	SELECT @totDebit=coalesce(SUM(ValoareLei),0) FROM [Rulaje] WHERE (IDDocument IN (SELECT IDDocument FROM RulajeDoc WHERE
		IDJurnal = @IDClasificare AND [CodUnitate] = @CodUnitate)) AND ContDebit= @cont
	SELECT @totCredit=coalesce(SUM(ValoareLei),0) FROM [Rulaje] WHERE (IDDocument IN (SELECT IDDocument FROM RulajeDoc WHERE
		 IDJurnal = @IDClasificare AND [CodUnitate] = @CodUnitate)) AND ContCredit=@cont 
	SET @s940 = @totDebit - @totCredit

	SET @cont ='8062.'+@Analitic
	SELECT @totDebit=coalesce(SUM(ValoareLei),0) FROM Rulaje WHERE  IDDocument IN (SELECT IDDocument FROM RulajeDoc d WHERE
		IDJurnal = @IDClasificare AND [CodUnitate] = @CodUnitate AND (d.DataDoc<=@PanaLa) ) AND ContDebit=@cont
	SELECT @totCredit=coalesce(SUM(ValoareLei),0) FROM Rulaje WHERE IDDocument IN (SELECT IDDocument FROM RulajeDoc d WHERE
		IDJurnal = @IDClasificare  AND [CodUnitate] = @CodUnitate AND (d.DataDoc<=@PanaLa) ) AND ContCredit=@cont
--	PRINT (@totDebit)
--	PRINT (@totCredit)

	SET @cont ='7701.'+@Analitic
	SELECT @totDebitPla=coalesce(SUM(ValoareLei),0) FROM [Rulaje] r WHERE (IDDocument IN (SELECT IDDocument FROM RulajeDoc WHERE
		IDJurnal = @IDClasificare AND [CodUnitate] = @CodUnitate AND DataDoc<@PanaLa )) AND ContDebit= @cont AND r.TipDocJust<>'IC'
	--SELECT coalesce(SUM(ValoareLei),0),TipDocJust FROM [Rulaje] r WHERE (IDDocument IN (SELECT IDDocument FROM RulajeDoc WHERE
	--	IDJurnal = @IDClasificare)) AND ContDebit= @cont AND r.TipDocJust<>'IC' GROUP BY TipDocJust

	SELECT @totCreditPla=coalesce(SUM(ValoareLei),0) FROM [Rulaje] r WHERE (IDDocument IN (SELECT IDDocument FROM RulajeDoc WHERE
		 IDJurnal = @IDClasificare AND [CodUnitate] = @CodUnitate AND DataDoc<@PanaLa)) AND ContCredit=@cont AND r.TipDocJust<>'IC'
	--SELECT coalesce(SUM(ValoareLei),0),TipDocJust FROM [Rulaje] r WHERE (IDDocument IN (SELECT IDDocument FROM RulajeDoc WHERE
		-- IDJurnal = @IDClasificare AND DataDoc<cast (@PanaLa as datetime))) AND ContCredit=@cont AND r.TipDocJust<>'IC' GROUP BY TipDocJust

--    PRINT (@totDebitPla)
--	PRINT (@totCreditPla)

	SELECT @totOrdonantat=coalesce(SUM(ValoareLei),0) FROM [Ordonantari] WHERE  (DataDoc<@PanaLa 
			OR (DataDoc=@PanaLa  AND IDOrdonantare<@CodDoc)) AND ArtAl Like @Analitic+'%'
			AND IDJurnal=@IDClasificare AND [CodUnitate] = @CodUnitate AND Achitat=0	
		
	SET @s944 = @totDebit + @totDebitPla - @totCredit - @totCreditPla - @totOrdonantat
    --PRINT (@totOrdonantat)



	SET @cont ='8066.'+@Analitic
	SELECT @totDebit=coalesce(SUM(ValoareLei),0) FROM Rulaje WHERE  IDDocument IN (SELECT IDDocument FROM RulajeDoc d WHERE
		IDJurnal = @IDClasificare AND [CodUnitate] = @CodUnitate AND (d.DataDoc<cast (@PanaLa as datetime) or ( d.DataDoc=cast(@PanaLa as datetime) AND (IDDocument<@CodDoc OR @CodDoc=0) )) ) AND ContDebit=@cont
	SELECT @totCredit=coalesce(SUM(ValoareLei),0) FROM Rulaje WHERE IDDocument IN (SELECT IDDocument FROM RulajeDoc d WHERE
		IDJurnal = @IDClasificare AND [CodUnitate] = @CodUnitate AND (d.DataDoc<cast (@PanaLa as datetime) or ( d.DataDoc=cast(@PanaLa as datetime) AND (IDDocument<@CodDoc OR @CodDoc=0) )) ) AND ContCredit=@cont
	SET @s950 =  @totDebit - @totCredit


	SET @cont ='8067.'+@Analitic
	SELECT @totDebit=coalesce(SUM(ValoareLei),0) FROM [Rulaje] WHERE (IDDocument IN (SELECT IDDocument FROM RulajeDoc WHERE
		IDJurnal = @IDClasificare AND [CodUnitate] = @CodUnitate)) AND ContDebit= @cont
	SELECT @totCredit=coalesce(SUM(ValoareLei),0) FROM [Rulaje] WHERE (IDDocument IN (SELECT IDDocument FROM RulajeDoc WHERE
		 IDJurnal = @IDClasificare AND [CodUnitate] = @CodUnitate)) AND ContCredit=@cont 
	SET @s960 = @totDebit - @totCredit

	 GOTO Gata
Eroare:
	 RAISERROR (@strEroare,16,-1)
Gata:
end

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[balcon_GenNotaContabilaBilant]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[balcon_GenNotaContabilaBilant]
GO


CREATE PROCEDURE [dbo].[balcon_GenNotaContabilaBilant]
	@idLista int,
	@cont TipCont,
	@CodUnitate smallint,
	@DeLaData datetime,
	@PanaLaData datetime,
	@An smallint,
	@iOrder smallint,
	@contDebit TipCont,
	@contCredit TipCont
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @strContStart as TipCont,@strContEnd as TipCont
	DECLARE @tipb as varchar(100)
	DECLARE @oct as varchar(100)
	DECLARE @jurnal int
	SET @strContStart=''
	SET @strContEnd=''
	SET @jurnal= -1
	SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='JRN' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
	if @tipb<>''
		SELECT @jurnal = IDJurnal FROM [Jurnale] WHERE Codificare=@tipb
		
	PRINT(@tipb)	
	
	SET @oct=''
	SELECT @oct=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='OCT' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
	
	PRINT(@oct)	

    SELECT @strContStart=LTRIM(RTRIM(Valoare)) FROM [ParamListe] WHERE IDLista=@idLista AND Cod='DLC' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
    SELECT @strContEnd=LTRIM(RTRIM(Valoare)) FROM [ParamListe] WHERE IDLista=@idLista AND Cod='PLC' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate

	if @An<=0
	SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.ContDebit, a.ContCredit, 
           b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
           b.TipFormula, c.Categorie, c.Tip, a.CodUnitate,a.IDDocument
	FROM  [Rulaje] a 
	      INNER JOIN  [RulajeDoc] b ON a.IDDocument = b.IDDocument 
	      INNER JOIN  [Conturi] c ON c.CodUnitate = a.CodUnitate AND a.ContDebit = c.Cont 
		  WHERE (a.CodUnitate = @CodUnitate) 
				 AND ((UPPER(@oct)='DEBIT' AND (@strContStart='' OR a.ContDebit>=@strContStart) AND (@strContEnd='' OR a.ContDebit<=@strContEnd)) 
				 OR (UPPER(@oct)='CREDIT' AND (@strContStart='' OR a.ContCredit>=@strContStart) AND (@strContEnd='' OR a.ContCredit<=@strContEnd))
				 OR (UPPER(@oct)=''))
				 AND (@cont='' OR (a.ContDebit = @cont OR a.ContCredit =@cont))
				 AND (b.DataDoc >= @DeLaData AND b.DataDoc<= @PanaLaData)
				 AND (@jurnal=-1 OR b.IDJurnal=@jurnal)
				 AND (@contDebit='' OR (a.ContDebit = @contDebit))
				 AND (@contCredit='' OR (a.ContCredit =@contCredit))
				 AND ContDebit<>'N/A' AND ContCredit<>'N/A' 

	ORDER BY CASE WHEN @iOrder = 1 THEN ContDebit 
	              WHEN @iOrder = 2 THEN ContCredit 
	              ELSE CAST(b.DataDoc AS VARCHAR(50)) END
	ELSE
	SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.ContDebit, a.ContCredit, 
           b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
           b.TipFormula, c.Categorie, c.Tip, a.CodUnitate,a.IDDocument
	FROM  [arhRulaje] a 
	      INNER JOIN  [arhRulajeDoc] b ON a.IDDocument = b.IDDocument and a.An=@An
	      INNER JOIN  [arhConturi] c ON c.CodUnitate = a.CodUnitate and a.An =c.An AND a.ContDebit = c.Cont 
	      		  WHERE (a.CodUnitate = @CodUnitate)
				 AND ((UPPER(@oct)='DEBIT' AND (@strContStart='' OR a.ContDebit>=@strContStart) AND (@strContEnd='' OR a.ContDebit<=@strContEnd)) 
				 OR (UPPER(@oct)<>'DEBIT' AND (@strContStart='' OR a.ContCredit>=@strContStart) AND (@strContEnd='' OR a.ContCredit<=@strContEnd))
				 OR (UPPER(@oct)=''))
				 AND (@cont='' OR (a.ContDebit = @cont OR a.ContCredit =@cont))
				 AND (b.DataDoc >= @DeLaData AND b.DataDoc<= @PanaLaData)
				 AND (@jurnal=-1 OR b.IDJurnal=@jurnal)
				 AND (@contDebit='' OR (a.ContDebit = @contDebit))
				 AND (@contCredit='' OR (a.ContCredit =@contCredit))
				 AND ContDebit<>'N/A' AND ContCredit<>'N/A' 

	ORDER BY CASE WHEN @iOrder = 1 THEN ContDebit 
	              WHEN @iOrder = 2 THEN ContCredit 
	              ELSE CAST(b.DataDoc AS VARCHAR(50)) END


END
GO



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmixAnsamble]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AmixAnsamble](
	[IDAnsamblu] [int] IDENTITY(1,1) NOT NULL,
	[IDMFixPrincipal] [int] NOT NULL,
	[IDMfixComponenta] [int] NOT NULL,
 CONSTRAINT [PK_AmixAnsamble] PRIMARY KEY CLUSTERED 
(
	[IDAnsamblu] ASC
),
 CONSTRAINT [IX_AmixAnsamble] UNIQUE NONCLUSTERED 
(
	[IDMfixComponenta] ASC,
	[IDMFixPrincipal] ASC
),
CONSTRAINT [FK_AmixAnsamble_AmixRegistru] FOREIGN KEY([IDMfixComponenta]) REFERENCES [dbo].[AmixRegistru] ([IDMijlocFix]),
CONSTRAINT [FK_AmixAnsamble_AmixRegistru1] FOREIGN KEY([IDMFixPrincipal]) REFERENCES [dbo].[AmixRegistru] ([IDMijlocFix])
) ON [PRIMARY]
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[amix_ImportVersiuneAnterioara]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[amix_ImportVersiuneAnterioara]
GO

CREATE PROCEDURE [dbo].[amix_ImportVersiuneAnterioara]
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint
AS
BEGIN
	DECLARE @strCodUnitate varchar(10)
	DECLARE @AnLunaCurenta int
	DECLARE @UltimaLuna int, @UltimulAn int
	SELECT @AnLunaCurenta = COALESCE(MAX([AnDeLucru]*12+[LunaDeLucru]),2400) FROM [ERP].[dbo].[a_arh_CFGAmix]
	SELECT @UltimaLuna = [LunaDeLucru], @UltimulAn=[AnDeLucru] FROM [ERP].[dbo].[a_arh_CFGAmix]
		WHERE [AnDeLucru]*12+[LunaDeLucru] = @AnLunaCurenta
		
	SET @AnLunaCurenta = @AnLunaCurenta+1	
	IF @UltimaLuna=12
	BEGIN
		SET @UltimulAn = @UltimulAn +1
		SET @UltimaLuna  = 1
	END
	ELSE
		SET @UltimaLuna = @UltimaLuna + 1	
	PRINT(@AnLunaCurenta)

	SET @strCodUnitate = RTRIM(LTRIM(CAST(@CodUnitate as varchar(10))))
	---importa din baza de date ERP
	BEGIN TRAN

    
    DELETE FROM [AmixAnsamble] WHERE IDMFixPrincipal IN (SELECT IDMijlocFix FROM [AmixRegistru] WHERE  [CodUnitate] =@CodUnitate) 
	IF @@ERROR != 0 GOTO EROARE	
    
    DELETE FROM [AmixComponenteSecundare] WHERE IDMijlocFix IN (SELECT IDMijlocFix FROM [AmixRegistru] WHERE  [CodUnitate] =@CodUnitate) 
	IF @@ERROR != 0 GOTO EROARE	
    
    DELETE FROM [AmixAmortizare] WHERE IDMijlocFix IN (SELECT IDMijlocFix FROM [AmixRegistru] WHERE  [CodUnitate] =@CodUnitate)
	IF @@ERROR != 0 GOTO EROARE	
    
    DELETE FROM [AmixRegistruBK] WHERE [CodUnitate]=@CodUnitate 
	IF @@ERROR != 0 GOTO EROARE	

    DELETE FROM [AmixRegistru] WHERE [CodUnitate]=@CodUnitate 
	IF @@ERROR != 0 GOTO EROARE	

    DELETE FROM [AmixDocumente] WHERE [CodUnitate]=@CodUnitate 
	IF @@ERROR != 0 GOTO EROARE	

    DELETE FROM [AmixCatalog] 
	IF @@ERROR != 0 GOTO EROARE	
	
	PRINT('Sterge Tabele - ok')
	

	INSERT INTO [AmixCatalog]
			   ([Grupa]
			   ,[Subgrupa]
			   ,[Clasa]
			   ,[Subclasa]
			   ,[Familia]
			   ,[DMinAmortizare]
			   ,[DMaxAmortizare]
			   ,[ContImobilizare]
			   ,[ContAmortizare]
			   ,[Denumire]
			   ,[Cod])
		 SELECT  
			   Grupa,
			   Subgrupa, 
			   Clasa, 
			   Subclasa, 
			   Familia, 
			   DMin,
			   DMax,
			   ContImobilizare,
			   ContAmortizare,
			   LTRIM(RTRIM(SUBSTRING([ACTIVE],1,150))) Denumire,
			   LTRIM(RTRIM(CAST(COALESCE(GRUPA,0) AS varchar(10)))) +'.' +
			   LTRIM(RTRIM(CAST(COALESCE(SUBGRUPA,0) AS varchar(10)))) +'.' +
			   LTRIM(RTRIM(CAST(COALESCE(CLASA,0) AS varchar(10)))) +'.' +
			   LTRIM(RTRIM(CAST(COALESCE(SUBCLASA,0) AS varchar(10)))) +'.' +
			   LTRIM(RTRIM(COALESCE(FAMILIA,'')))
			   FROM [ERP].[dbo].[a_act_CATALOG] a
			    
	IF @@ERROR != 0 GOTO EROARE					   
	SET IDENTITY_INSERT [AmixCatalog] ON
	INSERT INTO [AmixCatalog]
			   ([IDClasificare],
			    [Grupa]
			   ,[Subgrupa]
			   ,[Clasa]
			   ,[Subclasa]
			   ,[Familia]
			   ,[DMinAmortizare]
			   ,[DMaxAmortizare]
			   ,[ContImobilizare]
			   ,[ContAmortizare]
			   ,[Denumire]
			   ,[Cod])
		VALUES (0,  
			   0,
			   0, 
			   0, 
			   0, 
			   NULL, 
			   0,
			   0,
			   '',
			   '',
			   'Fara Clasa',
			   '0')
	
	SET IDENTITY_INSERT [AmixCatalog] OFF
	PRINT('catalog - ok')
	
	
	
    SET IDENTITY_INSERT [AmixRegistru] ON
   
    
	INSERT INTO [AmixRegistru]
   ([IDMijlocFix],[NrInventar],[Denumire],[ContAmortizare],[StocFaptic],[IDClasificare],
    [DataIntrare],
    [DataIesire],
    [CodUnitate],[IDDepartament],
    [IDStare],[ContImobilizare],[ContCheltuiala],
    [DurataAmortizare],[RegimAmortizare],[ValoareInventar],
    [PunereInFunctiune],
    [Compus],[Blocat],[IDGestionar],[ContTransfer],[ValoareAmortizata],[LuniAmortizate],[AmortizareLuna],[AmortizareAn])
	SELECT CAST(NRINV AS int),[NRINV],[DEN],LTRIM(RTRIM(CONTS)) +'.'+ LTRIM(RTRIM(CONTAN1)) + '.' +LTRIM(RTRIM(CONTAN2)),[StocFaptic], COALESCE(b.[IDClasificare],0) IDClasificare,
	CAST(CASE WHEN COALESCE(ANI,0)=0 THEN 1900 ELSE ANI END AS VARCHAR(4)) + '-' + CAST(CASE WHEN COALESCE(LNI,0)=0 THEN 1 ELSE LNI END AS VARCHAR(2)) +'-01',
	CAST(CASE WHEN COALESCE(ANO,0)=0 THEN 1900 ELSE ANO END AS VARCHAR(4)) + '-' + CAST(CASE WHEN COALESCE(LNO,0)=0 THEN 1 ELSE LNO END AS VARCHAR(2)) +'-01',
	--DATEADD(month,DURN*12,CAST(CAST(CASE WHEN COALESCE(ANI,0)=0 THEN 1900 ELSE ANI END AS VARCHAR(4)) + ''-'' + CAST(CASE WHEN COALESCE(LNI,0)=0 THEN 1 ELSE LNI END AS VARCHAR(2)) +''-01'' AS DATETIME)),
	@CodUnitate,c.[IDDepartament], 
	CASE WHEN CONS='A' THEN 1 WHEN CONS='I' THEN 3 WHEN CONS='E' THEN 5 WHEN CONS='O' THEN 2 ELSE 5 END  [IDStare],NULL,NULL,
	DURN*12,Regim,ValInv,
	DATEADD(month,1,CAST(CAST(CASE WHEN COALESCE(ANI,0)=0 THEN 1900 ELSE ANI END AS VARCHAR(4)) + '-' + CAST(CASE WHEN COALESCE(LNI,0)=0 THEN 1 ELSE LNI END AS VARCHAR(2)) + '-01' AS DATETIME)),
	0,0,1,null,COALESCE(vala,0),CASE WHEN COALESCE(ANO*12+LNO,@AnLunaCurenta)<@AnLunaCurenta  THEN COALESCE(ANO*12+LNO,@AnLunaCurenta) - COALESCE(ANI*12 +LNI,1970*24) ELSE @AnLunaCurenta - (COALESCE(ANI,1970)*12+COALESCE(LNI,0)) END [LuniAmortizate],COALESCE(RA,0),COALESCE(AMOAN,0)
	FROM [ERP].[dbo].[a_act_MIX] a 
	LEFT OUTER JOIN [AmixCatalog] b ON coalesce(b.Grupa,0)=coalesce(a.Grupa,0) AND COALESCE(b.Subgrupa,0)=COALESCE(a.Subgrupa,0) and COALESCE(b.clasa,0)=COALESCE(a.clasa,0) and Coalesce(b.subclasa,0)=coalesce(a.subclasa,0) and coalesce(b.familia,'')=coalesce(a.familia,'') COLLATE  SQL_Latin1_General_CP1_CI_AS
    JOIN [Departamente] c  ON LTRIM(RTRIM(a.SEC)) = LTRIM(RTRIM(c.Cod))     

	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [AmixRegistru] OFF
	PRINT('Import Registru curent - ok')
	COMMIT
	DECLARE doc_cursor CURSOR FOR  SELECT [AnDeLucru],[LunaDeLucru] FROM [ERP].[dbo].[a_arh_CFGAmix]
  	
    declare @LunaLucru smallint
  	declare @AnLucru smallint
    declare @LunaLucruStr varchar(2)
  	declare @AnLucruStr VARCHAR(4)

  
  	OPEN doc_cursor
  	FETCH NEXT FROM doc_cursor INTO @AnLucru,@LunaLucru
  	SET @LunaLucruStr = CAST(@LunaLucru as varchar(2))
  	SET @AnLucruStr = CAST(@AnLucru as varchar(4))
  	IF LEN(@LunaLucruStr)=1 
  		SET @LunaLucruStr = '0' + @LunaLucruStr
  	DECLARE @Comanda varchar(4000) 	
  	WHILE (@@FETCH_STATUS =0)
  	BEGIN		  		
  	

		SET @Comanda = 'INSERT INTO [AmixRegistruBK]
		   ([IDMijlocFix],[NrInventar],[Denumire],[ContAmortizare],[StocFaptic],[IDClasificare],
			[DataIntrare],
			[DataIesire],
			[CodUnitate],[IDDepartament],
			[IDStare],[ContImobilizare],[ContCheltuiala],
			[DurataAmortizare],[RegimAmortizare],[ValoareInventar],
			[PunereInFunctiune],
			[Compus],[Blocat],[IDGestionar],[ContTransfer],[ValoareAmortizata],[LuniAmortizate],[AmortizareLuna],[AmortizareAn],[Luna],[An])
			SELECT CAST(NRINV AS int),[NRINV],[DEN],LTRIM(RTRIM(CONTS)) +''.''+ LTRIM(RTRIM(CONTAN1)) + ''.'' +LTRIM(RTRIM(CONTAN2)),[StocFaptic], b.[IDClasificare],
			CAST(CASE WHEN COALESCE(ANI,0)=0 THEN 1900 ELSE ANI END AS VARCHAR(4)) + ''-'' + CAST(CASE WHEN COALESCE(LNI,0)=0 THEN 1 ELSE LNI END AS VARCHAR(2)) +''-01'',
			CAST(CASE WHEN COALESCE(ANO,0)=0 THEN 1900 ELSE ANO END AS VARCHAR(4)) + ''-'' + CAST(CASE WHEN COALESCE(LNO,0)=0 THEN 1 ELSE LNO END AS VARCHAR(2)) +''-01'','
			+CAST(@CodUnitate AS varchar(10))+',c.[IDDepartament], 
			CASE WHEN CONS=''A'' THEN 1 WHEN CONS=''I'' THEN 3 WHEN CONS=''E'' THEN 5 WHEN CONS=''O'' THEN 2 ELSE 5 END  [IDStare],NULL,NULL,
			DURN*12,Regim,ValInv,
			DATEADD(month,1,CAST(CAST(CASE WHEN COALESCE(ANI,0)=0 THEN 1900 ELSE ANI END AS VARCHAR(4)) + ''-'' + CAST(CASE WHEN COALESCE(LNI,0)=0 THEN 1 ELSE LNI END AS VARCHAR(2)) + ''-01'' AS DATETIME)),
			0,0,1,null,COALESCE(vala,0),CASE WHEN COALESCE(ANO*12+LNO,'+CAST(@AnLunaCurenta AS varchar(10))+')<'+CAST(@AnLunaCurenta AS Varchar(10))+'  THEN COALESCE(ANO*12+LNO,'+
			CAST(@AnLunaCurenta AS Varchar(10)) +') - COALESCE(ANI*12 +LNI,1970*24) ELSE '+CAST(@AnLunaCurenta AS VARCHAR(10))+' - (COALESCE(ANI,1970)*12+COALESCE(LNI,0)) END [LuniAmortizate],COALESCE(RA,0),COALESCE(AMOAN,0),'
			+@lunalucrustr +','+@AnLucruStr +
			' FROM [ERP].[dbo].[a_act_MIX] a 
			JOIN [AmixCatalog] b ON b.Grupa=a.Grupa AND b.Subgrupa=a.Subgrupa and b.clasa=a.clasa and Coalesce(b.subclasa,0)=coalesce(a.subclasa,0) and coalesce(b.familia,'''')=coalesce(a.familia,'''')
			JOIN [Departamente] c  ON a.SEC = c.Cod'     
		PRINT(@Comanda)
		EXEC(@Comanda) 
		IF @@ERROR <>0
		BEGIN
			CLOSE doc_cursor
  			DEALLOCATE doc_cursor
			print @comanda
			GOTO Eroare1
		END  				
		
		SET @comanda = 
		'INSERT INTO [AmixAmortizare]
			([IDMijlocFix],[Luna],[An],[ValoareInventar],[ValoareAmortizata],[ValoareRamasa],[AmortizareAn],[AmortizareLuna],
			[DurataNormata],[LuniAmortizate],[Blocat],[IDBK])
  			SELECT 
  			CAST(NRINV AS int) IDMijlocFix,'+@LunaLucruStr+','+@AnLucruStr+',valinv,COALESCE(vala,0),valinv-COALESCE(vala,0),'+
  			'COALESCE(amoan,0)  AS [AmortizareAn],'+
  			'COALESCE(ra,0) AS [AmortizareLuna],COALESCE(durn,0),('
  			+@AnLucruStr + '*12+' + @LunaLucruStr + ')-COALESCE(ANI*12+LNI,1970*12)' + 
  			'[LuniAmortizate],0,NULL
			FROM  [ERP].[dbo].[a_' + @AnLucruStr + @LunaLucruStr + 'a_arh_MIX] ' + 
			' WHERE CAST(NRINV AS int) IN (SELECT IDMijlocFix FROM [AmixRegistru])'
		print @comanda
  		execute(@comanda) 
  		IF @@ERROR<>0
		BEGIN
			CLOSE doc_cursor
  			DEALLOCATE doc_cursor
			print @comanda
			GOTO Eroare1
		END  		
		SET @comanda = 
		'INSERT INTO [AmixDocumente]([NrInventar],[Denumire],[IdClasificare],[IDDepartament],[ContAmortizare],[ValoareInventar],[DurataAmortizare]'+
           ',[RegimAmortizare],[DataDoc],[NumarDoc],[ComisiaRec],[DurExtindere],[CodUnitate],[IDMod],[IDTipDocument],[ContCheltuiala]'+
           ',[ContImobilizare],[Luna],[Anul],[AmortizareLuna],[AmortizareAn],[ValoareAmortizata],[ContTransfer],[IDMijlocFix],[PunereInFunctiune],[Compus]'+
           ',[AmortizarePrimulAn],[IDGestionar],[ValoareSuplimentara],[LuniAmortizate],[IDBK])'+
		'SELECT REPLACE(NRINV,''.'',''''),COALESCE(DEN,''''),COALESCE(b.[IDClasificare],0),COALESCE(COALESCE(c.[IDDepartament],c1.[IDDepartament]),0),Conts,Coalesce(ValInv,0),COALESCE(DurN*12,0),'+
		'COALESCE(Regim,0),COALESCE(DataDoc,CAST(CAST(An as varchar(4)) + ''-'' + CAST(LUNA AS varchar(2)) + ''-01''  AS DATETIME)),NumarDoc,COALESCE(ComisiaRec,''''),COALESCE(DurExtindere,0),'+@strCodUnitate+',CASE WHEN COALESCE(CDEL,'''')=''C'' THEN 4 ELSE 0 END,Tip,NULL,'+
		'NULL,Luna,An,Coalesce(RA,0),Coalesce(RA*12,0),COALESCE(VALA,0),Conttr,CAST(REPLACE(NRINV,''.'','''') AS INT),DATEADD(month,1,COALESCE(DataDoc,CAST(CAST(An as varchar(4)) + ''-'' + CAST(LUNA AS varchar(2)) + ''-01''  AS DATETIME))),0,'+
		'0,1,0,0,NULL '+
		'FROM  [ERP].[dbo].[a_' + @AnLucruStr + @LunaLucruStr + 'a_arh_MIS] a 
		LEFT OUTER JOIN [AmixCatalog] b ON coalesce(b.Grupa,0)=coalesce(a.Grupa,0) AND coalesce(b.Subgrupa,0)=coalesce(a.Subgrupa,0) and coalesce(b.clasa,0)=coalesce(a.clasa,0) and coalesce(b.subclasa,0)=coalesce(a.subclasa,0) and coalesce(b.familia,'''')=coalesce(a.familia,'''') '           
		+'LEFT OUTER JOIN [Departamente] c  ON LTRIM(RTRIM(a.SECI)) = LTRIM(RTRIM(c.COD))'  
		+'LEFT OUTER JOIN [Departamente] c1  ON LTRIM(RTRIM(a.SECO)) = LTRIM(RTRIM(c1.COD))'  
		
		print @comanda
  		execute(@comanda)
  		IF @@ERROR<>0
		BEGIN
			CLOSE doc_cursor
  			DEALLOCATE doc_cursor
			print @comanda
			GOTO Eroare1
		END  		
		
		
		
  		FETCH NEXT FROM doc_cursor INTO @AnLucru,@LunaLucru		
  		SET @LunaLucruStr = CAST(@LunaLucru as varchar(2))
  		SET @AnLucruStr = CAST(@AnLucru as varchar(4))
  		IF LEN(@LunaLucruStr)=1 
  			SET @LunaLucruStr = '0' + @LunaLucruStr  		
  	end ---
  		
	--print 'dealocare'
	CLOSE doc_cursor
	DEALLOCATE doc_cursor

	SET @LunaLucru = @LunaLucru + 1
	IF @LunaLucru=13 
	BEGIN 
		SET @AnLucru=@AnLucru + 1
		SET @LunaLucru = 1
	END		
  	SET @LunaLucruStr = CAST(@LunaLucru as varchar(2))
  	SET @AnLucruStr = CAST(@AnLucru as varchar(4))
  	IF LEN(@LunaLucruStr)=1 
  		SET @LunaLucruStr = '0' + @LunaLucruStr  

		INSERT INTO [AmixAmortizare]
			([IDMijlocFix],[Luna],[An],[ValoareInventar],[ValoareAmortizata],[ValoareRamasa],[AmortizareAn],[AmortizareLuna],[DurataNormata],[LuniAmortizate],[Blocat],[IDBK])
  			SELECT CAST(NRINV AS int),@LunaLucru ,@AnLucruStr,valinv,vala,valinv-vala,
  			COALESCE(amoan,0)  AS [AmortizareAn],
  			COALESCE(ra,0) AS [AmortizareLuna],COALESCE(durn,0),
  			CASE 
  			   WHEN COALESCE(RA,0)=0 THEN COALESCE(DURN,0)*12 
  			   ELSE 
  			   CASE 
  			      WHEN ROUND(VALA/RA,0)>32767 THEN 32767 
  			      ELSE ROUND(VALA/RA,0) 
  			   END 
  			END [LuniAmortizate],0,NULL
			FROM  [ERP].[dbo].[a_act_MIX]  
			 WHERE CAST(NRINV AS int) IN (SELECT IDMijlocFix FROM [AmixRegistru] WHERE RegimAmortizare!=0)
  		IF @@ERROR<>0
		BEGIN
			CLOSE doc_cursor
  			DEALLOCATE doc_cursor
			print @comanda
			GOTO Eroare1
		END  		


	UPDATE [AmixSetup] SET [LunaCurenta] = @UltimaLuna, [AnulCurent]=@UltimulAn WHERE 
		[CodUnitate] = @CodUnitate
	

	GOTO Gata
	EROARE:
		ROLLBACK
		RAISERROR ('Eroare la preluarea datelor!',16,-1)
		GOTO Gata
	EROARE1:
		RAISERROR ('!!',16,-1)	
		GOTO Gata
	Gata:
	
END



GO


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ExBugSetup' AND COLUMN_NAME = 'AfiseazaParteneriFolositi')
BEGIN
	ALTER TABLE dbo.ExBugSetup ADD AfiseazaParteneriFolositi bit DEFAULT(0)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[exBug_AfiseazaParteneriFolositi]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[exBug_AfiseazaParteneriFolositi]
GO

CREATE PROCEDURE [dbo].[exBug_AfiseazaParteneriFolositi]
	@CodUnitate smallint, 
	@Afiseaza bit
AS
BEGIN
	SET NOCOUNT ON;

	SELECT * FROM Parteneri p
	WHERE CodUnitate = @CodUnitate
	AND ((@Afiseaza=0) OR EXISTS (SELECT * FROM [Ordonantari] o WHERE o.CodUnitate=@CodUnitate AND o.IDFurnizor=p.IDPartener))
END

GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TiparesteParteneri]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TiparesteParteneri]
GO

CREATE PROCEDURE [dbo].[TiparesteParteneri]
	@CodUnitate smallint,
	@ParteneriFolositi bit
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT [IDPartener]
      ,[CUI]
      ,p.[Denumire]
      ,b.Denumire as DenumireBanca
      ,[ContBancar]
      ,[Cont]
      ,[CodUnitate]
      ,[TipPartener]
      ,j.Nume AS Judet
      ,l.Nume as Localitate
      ,[Strada]
      ,[Nr]
      ,[AtributFiscal]
      ,[JudetRegComert]
      ,[NrRegComert]
      ,[AnRegComert]
      ,[NumeDelegat]
      ,[SerieAI]
      ,[NrAI]
      ,[MijlocTransport]
      ,[NrMijlocTransport]
      ,p.[CodPostal]
      ,[Telefon]
      ,[Fax]
      ,[ContClient]
      ,[ConditiiDePlata]
      ,t.NUME as Tara
      ,[IDContactDelegat]
      ,[IDMijlocTransport]
      ,[ScadentaFacturiClient]
      ,[ScadentaFacturiFurnizor]
      ,[ScadentaFacturiSfarsitLuna] FROM Parteneri p
      LEFT OUTER JOIN Banci b ON p.IDBanca = b.IdBanca
      LEFT OUTER JOIN Judete j ON p.IDJudet = j.IDJudet
      LEFT OUTER JOIN Tari t ON t.IDTara = p.IDTara
      LEFT OUTER JOIN Localitati l ON l.IDLocalitate = p.IDLocalitate
	WHERE CodUnitate = @CodUnitate
	AND ((@ParteneriFolositi=0) OR EXISTS (SELECT * FROM [Ordonantari] o WHERE o.CodUnitate=@CodUnitate AND o.IDFurnizor=p.IDPartener))
END

GO


IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[AmixViewDocumente]'))
DROP VIEW [dbo].[AmixViewDocumente]
GO

CREATE VIEW [dbo].[AmixViewDocumente]
AS
SELECT     dbo.AmixDocumente.NumarDoc, dbo.AmixDocumente.DataDoc, dbo.AmixDocumente.IDTipDocument, dbo.TipDocument.Denumire AS DenumireDocument, 
                      dbo.Departamente.Nume AS LocFunctionare, dbo.AmixDocumente.NrInventar, dbo.AmixDocumente.Denumire, dbo.AmixCatalog.Cod AS codClasificare, 
                      dbo.AmixTipModDocument.Denumire AS Modalitate, 
					  COALESCE(dbo.AmixDocumente.ContAmortizare,'--nul--') ContAmortizare, dbo.AmixDocumente.ContCheltuiala, 
					  COALESCE(dbo.AmixDocumente.ContImobilizare,'--nul--') ContImobilizare, 
                      dbo.AmixDocumente.ContTransfer, dbo.AmixDocumente.PunereInFunctiune, dbo.AmixGestionari.Nume AS Gestionar, dbo.AmixDocumente.ValoareInventar, dbo.AmixDocumente.DurataAmortizare, 
                      CASE WHEN dbo.AmixDocumente.RegimAmortizare = 0 THEN 'Fara Amortizare' WHEN dbo.AmixDocumente.RegimAmortizare = 1 THEN 'Amortizare Liniara' WHEN dbo.AmixDocumente.RegimAmortizare
                       = 2 THEN 'Amortizare Accelerata' WHEN dbo.AmixDocumente.RegimAmortizare = 3 THEN 'Amortizare degresiva' ELSE '' END AS RegimAmortizare, dbo.AmixDocumente.ComisiaRec AS Observatii, 
                      dbo.AmixDocumente.CodUnitate, dbo.AmixDocumente.Luna, dbo.AmixDocumente.Anul, dbo.AmixDocumente.AmortizareLuna, dbo.AmixDocumente.AmortizareAn, 
                      dbo.AmixDocumente.ValoareAmortizata, dbo.AmixDocumente.ValoareSuplimentara, dbo.AmixDocumente.IDMijlocFix, 
                      CASE WHEN dbo.AmixDocumente.Compus = 0 THEN 'Mijloc Fix' WHEN dbo.AmixDocumente.Compus = 1 THEN 'Mijloc Fix Compus' WHEN dbo.AmixDocumente.Compus = 2 THEN 'Mijloc Fix Componenta'
                       ELSE '' END AS TipMijlocFix, dbo.AmixDocumente.Compus, dbo.AmixDocumente.AmortizarePrimulAn, dbo.AmixDocumente.Categorie, dbo.AmixDocumente.ContTransferForSuperior, 
                      dbo.AmixDocumente.IDUnitateMasura, dbo.AmixDocumente.CantitateOI, um.Denumire AS DenumireUM
FROM         dbo.AmixDocumente INNER JOIN
                      dbo.TipDocument ON dbo.AmixDocumente.IDTipDocument = dbo.TipDocument.IDTipDocument LEFT OUTER JOIN
                      dbo.Departamente ON dbo.AmixDocumente.IDDepartament = dbo.Departamente.IDDepartament LEFT OUTER JOIN
                      dbo.AmixCatalog ON dbo.AmixDocumente.IdClasificare = dbo.AmixCatalog.IDClasificare LEFT OUTER JOIN
                      dbo.AmixTipModDocument ON dbo.AmixDocumente.IDMod = dbo.AmixTipModDocument.IDMod LEFT OUTER JOIN
                      dbo.AmixGestionari ON dbo.AmixDocumente.IDGestionar = dbo.AmixGestionari.IDGestionar LEFT OUTER JOIN
                      dbo.UnitatiMasura AS um ON um.IDUnitateMasura = dbo.AmixDocumente.IDUnitateMasura

GO




IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[amix_GetDocumente]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[amix_GetDocumente]
GO

CREATE PROCEDURE [dbo].[amix_GetDocumente]
	@CodUnitate smallint, 
	@MijloaceFixe bit,
	@DeLaData datetime,
	@PanaLaData datetime,
	@ContImobilizare varchar(2000),
	@ContAmortizare varchar(2000),
	@LocFunctionare varchar(4000),
	@Gestionar varchar(2000),
	@DenumireDocument varchar(2000)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * FROM [AmixViewDocumente] WHERE [CodUnitate]=@CodUnitate 
	AND (@MijloaceFixe = 0 OR [Compus]<3)
	AND (@MijloaceFixe = 1 OR [Compus]>=3)
	AND [Anul]*100 + [Luna]>= YEAR(@DeLaData)*100 + MONTH(@DeLaData)
	AND [Anul]*100 + [Luna]<= YEAR(@PanaLaData)*100 + MONTH(@PanaLaData)
	AND (LEN(LTRIM(RTRIM(@ContImobilizare)))=0 OR (ContImobilizare IN (SELECT fart.value FROM dbo.SplitParameters(@ContImobilizare,',') fart)))
	AND (LEN(LTRIM(RTRIM(@ContAmortizare)))=0 OR (ContAmortizare IN (SELECT fart.value FROM dbo.SplitParameters(@ContAmortizare,',') fart)))
	AND (LEN(LTRIM(RTRIM(@LocFunctionare)))=0 OR (LocFunctionare IN (SELECT fart.value FROM dbo.SplitParameters(@LocFunctionare,',') fart)))
	AND (LEN(LTRIM(RTRIM(@Gestionar)))=0 OR (Gestionar IN (SELECT fart.value FROM dbo.SplitParameters(@Gestionar,',') fart)))
	AND (LEN(LTRIM(RTRIM(@DenumireDocument)))=0 OR (DenumireDocument IN (SELECT fart.value FROM dbo.SplitParameters(@DenumireDocument,',') fart)))
END

GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[amix_InchideLuna2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[amix_InchideLuna2]
GO

CREATE PROCEDURE [dbo].[amix_InchideLuna2] 
	-- Add the parameters for the stored procedure here
	@Luna smallint, 
	@An smallint,
	@CodUnitate smallint,
	@IDDocument int, --daca document e zero pe toate
	@InchideLuna bit --daca se inchide luna sau nu
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;
	-- numarul mijloacelor fixe cu acelasi numar de inventar
	DECLARE @MaxDurata int
	DECLARE @Index int
	DECLARE @Compus int
	DECLARE @UltimaZiLuna datetime
	DECLARE @LunaCurenta smallint 
	DECLARE @AnCurent smallint

	SELECT @LunaCurenta =LunaCurenta, @AnCurent=AnulCurent  FROM AmixSetup WHERE CodUnitate=@CodUnitate

	SET @UltimaZiLuna = DATEADD(day,-1,DATEADD(month,1,CAST(CAST(@An AS varchar(4)) + '-' + CAST(@Luna AS varchar(2)) + '-01' AS datetime)))
 
 
	IF @IDDocument=0
		SET @Compus = 1
	ELSE
		--daca documentul a fost sters le actualizeaza pe toate!
		SELECT @Compus = ISNULL([Compus],1) FROM [AmixDocumente] WHERE  [IDDocument]=@IDDocument	
	PRINT('Compus=' + cast(@Compus as varchar(2)))
	--sterge mijloacele fixe introduse in luna curenta
	BEGIN TRAN
	
	---sterge din tabela de amortizari inregistrarile generate
	-- daca suntem dupa luna curenta
	IF @AnCurent*12 + @LunaCurenta <= @An*12+@Luna
	BEGIN
		DELETE FROM [AmixAmortizare] WHERE [IDMijlocFix] IN (SELECT [IDMijlocFix] FROM [AmixRegistru]
			WHERE [CodUnitate]=@CodUnitate) AND [An] = @An AND [Luna]=@Luna
		IF @@ERROR <>0 GOTO Eroare  
	
		DELETE FROM [AmixRegistruBK] WHERE  [CodUnitate]=@CodUnitate AND [An] = @An AND [Luna]=@Luna
		IF @@ERROR <>0 GOTO Eroare  	

		---------------------------------------------------------------------------------------------------
		----------------------------------------Backup-----------------------------------------------------
		--- se salveaza toate mijloacele fixe
		INSERT INTO [AmixRegistruBK]
			   ([IDMijlocFix],[NrInventar],[Denumire],[ContAmortizare],[StocFaptic],[IDClasificare],[DataIntrare],[DataIesire],[CodUnitate],[IDDepartament],[IDStare]
				,[ContImobilizare],[ContCheltuiala],[DurataAmortizare],[RegimAmortizare],[ValoareInventar],[PunereInFunctiune],[Compus] ,[Blocat]
				,[IDGestionar],[ContTransfer],[ValoareAmortizata],[LuniAmortizate],[AmortizareLuna],[AmortizareAn],[InFolosinta],[CantitateOI]
				,[Categorie],[IDUnitateMasura],Luna,An,[ContTransferForSuperior],[IDJurnal],[ValoareIntrataTransfer])
				SELECT [IDMijlocFix],[NrInventar],[Denumire],[ContAmortizare],[StocFaptic],[IDClasificare],[DataIntrare],[DataIesire],[CodUnitate],[IDDepartament],[IDStare]
				,[ContImobilizare],[ContCheltuiala],[DurataAmortizare],[RegimAmortizare],[ValoareInventar],[PunereInFunctiune],[Compus] ,[Blocat]
				,[IDGestionar],[ContTransfer],[ValoareAmortizata],[LuniAmortizate],[AmortizareLuna],[AmortizareAn],[InFolosinta],[CantitateOI]
				,[Categorie],[IDUnitateMasura],@LunaCurenta,@AnCurent,[ContTransferForSuperior],[IDJurnal],[ValoareIntrataTransfer]
			   FROM [AmixRegistru] a 
			WHERE  [CodUnitate]=@CodUnitate 
		IF @@ERROR <>0 GOTO Eroare  	

		UPDATE [AmixDocumente] SET [IDBK] = b.[IDBK] FROM [AmixRegistruBK] b 
		WHERE [AmixDocumente].[NrInventar] = b.[NrInventar] AND [AmixDocumente].[CodUnitate] = b.[CodUnitate]
		IF @@ERROR <>0 GOTO Eroare  	

		----------------------------------------Backup-----------------------------------------------------
	
	END
	
	---------------------------------------------------------------------------------------------------

	--nu se pot sterge inregistrarile din registru pentru ca sunt legate de amortizare
	-- ele se updateaza mijloacele fixe existente
	-------------------------------------------------------------------------------
	UPDATE [AmixRegistru] 
			SET [NrInventar] = d.[NrInventar],
			[Denumire]=d.[Denumire],
			[ContAmortizare]=d.[ContAmortizare],
			[StocFaptic]=1,
			[IDClasificare]=d.[IDClasificare],
            [DataIntrare]=CASE WHEN d.Compus<3 THEN DATEADD(month,1,d.[PunereInFunctiune]) ELSE d.[DataDoc] END,
            [DataIesire]=DATEADD(month,d.[DurataAmortizare]-d.[LuniAmortizate],d.[PunereInFunctiune]),
			[CodUnitate]=@CodUnitate,
			[IDDepartament]=d.[IDDepartament],
			[IDStare]=5,
			[ContImobilizare]=d.[ContImobilizare],
			[ContCheltuiala]=d.[ContCheltuiala],
			[DurataAmortizare]=d.[DurataAmortizare],
			[RegimAmortizare]=d.[RegimAmortizare],
			[ValoareInventar]=d.[ValoareInventar],
			[PunereInFunctiune]=d.[PunereInFunctiune],[Compus]=d.[Compus],
			[IDGestionar]=d.[IDGestionar],
			[AmortizareLuna] = d.[AmortizareLuna],
			[AmortizareAn] = d.[AmortizareAn], -- de fapt este vorba de amortizarea in primul an
			[ValoareAmortizata] = d.ValoareAmortizata,
			[IDUnitateMasura] = d.[IDUnitateMasura],
			[CantitateOI] = d.CantitateOI,
			[InFolosinta] = d.[InFolosinta],
			[ContTransferForSuperior] = d.[ContTransferForSuperior]
			FROM [AmixDocumente] d
	WHERE  
		d.[Anul] = @An AND d.[Luna]=@Luna AND d.[CodUnitate]=@CodUnitate AND d.[IDTipDocument]=2 AND d.[IDMijlocFix] = [AmixRegistru].[IDMijlocFix] AND d.[IDMijlocFix] IS NOT NULL
		AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
	IF @@ERROR <>0 GOTO Eroare  	
	--pentru modernizare
	PRINT('actualizarile pentru modernizare in registru')
    --atentie se modifica si mortizarea lunara si Amorizarea anuala
	UPDATE [AmixRegistru] 
			SET [DurataAmortizare]=d.[DurataAmortizare]+ d.[DurExtindere],[ValoareInventar]=d.[ValoareInventar] + d.[ValoareSuplimentara],
            [AmortizareLuna] = CASE WHEN [AmixRegistru].[RegimAmortizare]= 1 THEN d.[AmortizareLuna]
				WHEN [AmixRegistru].[RegimAmortizare]= 2 THEN d.[AmortizareLuna]
				WHEN [AmixRegistru].[RegimAmortizare]= 3 THEN  -- accelerata
					CASE WHEN DATEDIFF(month,[AmixRegistru].[PunereInFunctiune],@UltimaZiLuna)<12 THEN d.[AmortizareLuna]
						 WHEN DATEDIFF(month,[AmixRegistru].[PunereInFunctiune],@UltimaZiLuna)=12 THEN d.[AmortizareAn] - d.[AmortizareLuna]*11
						ELSE (d.[ValoareInventar] - d.[ValoareAmortizata] - d.[AmortizareAn])/(d.[DurataAmortizare]-12)
					END 
				ELSE 0
				END
			FROM [AmixDocumente] d
	WHERE  
		d.[Anul] = @An AND d.[Luna]=@Luna AND d.[CodUnitate]=@CodUnitate AND d.[IDTipDocument]=8 AND d.[IDMijlocFix] = [AmixRegistru].[IDMijlocFix] AND d.[IDMijlocFix] IS NOT NULL
		AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
	IF @@ERROR <>0 GOTO Eroare  

	--se adauga mfixe noi
	------------------------------------------------------------------------------
	PRINT('Adauga noi MF')
	INSERT INTO [AmixRegistru]
           ([NrInventar],[Denumire],[ContAmortizare],[StocFaptic],[IDClasificare]
           ,[DataIntrare],[DataIesire],[CodUnitate],[IDDepartament],[IDStare],[ContImobilizare],[ContCheltuiala],[DurataAmortizare],[RegimAmortizare],[ValoareInventar],
			[PunereInFunctiune],[Compus],[IDGestionar],[AmortizareLuna],[AmortizareAn],[ValoareAmortizata],[CantitateOI],[IDUnitateMasura],[InFolosinta])
	(SELECT [NrInventar],[Denumire],[ContAmortizare],1,[IDClasificare],
	 CASE WHEN Compus<3 THEN DATEADD(month,1,[PunereInFunctiune]) ELSE [PunereInFunctiune] END,
	 DATEADD(month,[DurataAmortizare],[PunereInFunctiune]),
	 @CodUnitate,[IDDepartament],5,[ContImobilizare],[ContCheltuiala],[DurataAmortizare],[RegimAmortizare],[ValoareInventar],
	 [PunereInFunctiune],[Compus],[IDGestionar],[AmortizareLuna],[AmortizareAn],[ValoareAmortizata],[CantitateOI],[IDUnitateMasura],[InFolosinta]
	 FROM [AmixDocumente] ad
	 WHERE [CodUnitate]=@CodUnitate AND [IDTipDocument]=2 AND [Anul]=@An AND [Luna]=@Luna
		AND ad.[IDMijlocFix] IS NULL
		AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
		AND NOT EXISTS(SELECT * FROM AmixRegistru ar WHERE ar.CodUnitate=ad.CodUnitate AND ar.NrInventar=ad.NrInventar))
		
	IF @@ERROR <>0 GOTO Eroare  
	--------------------------------------------------------------------------------
	PRINT('Face legatura cu registrul pentru toate documentele')

	UPDATE [AmixDocumente] SET [IDMijlocFix] = a.[IDMijlocFix] FROM [AmixRegistru] a
		WHERE a.[NrInventar]=[AmixDocumente].[NrInventar] AND
			  [AmixDocumente].[CodUnitate]=@CodUnitate AND [AmixDocumente].[Anul]=@An AND [AmixDocumente].[Luna]=@Luna AND [AmixDocumente].[IDMijlocFix] IS NULL
			  AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
	
	IF @@ERROR <>0 GOTO Eroare  

	--Procesare Mijloace fixe compuse
	-------------------------------------------------------------------------
	IF @Compus>=1
	BEGIN
		Print('Prelucrare mijloace fixe compuse')
		UPDATE [AmixRegistru]
		SET [DataIntrare]= a2.[DataIntrare],
			[DataIesire] = a2.[DataIesire],
			[ValoareInventar] = a2.[ValoareInventar],
			[DurataAmortizare] = a2.[DurataAmortizare],
			[PunereInFunctiune] = a2.[PunereInFunctiune]
		FROM (SELECT b.[IDMFixPrincipal] AS [IDMijlocFix], 
				MIN([PunereInFunctiune]) AS [PunereInFunctiune],
				DATEDIFF(month,MIN([DataIntrare]),MAX([DataIesire]))+1 AS [DurataAmortizare],			
				SUM([ValoareInventar]) AS [ValoareInventar],
				MAX([DataIesire]) AS [DataIesire],
				MIN([DataIntrare]) AS [DataIntrare]
				FROM [AmixRegistru] a JOIN [AmixAnsamble] b ON a.[IDMijlocFix] = b.[IDMfixComponenta] 
				WHERE a.[Compus] = 2
				GROUP BY b.[IDMFixPrincipal]) a2
		WHERE 
		[Compus] = 1 AND 
		[CodUnitate]=@CodUnitate AND 
		[AmixRegistru].[IDMijlocFix] = a2.[IDMijlocFix]
		
		IF @@ERROR <>0 GOTO Eroare
	END

	Print('Conservare mijloc fix')
	----Conservare mijloc fix----------------------------------------
	UPDATE [AmixRegistru] 
			SET [IDStare] = 6
			FROM [AmixDocumente] d
	WHERE  
			d.[Anul] = @An AND d.[Luna]=@Luna AND d.[CodUnitate]=@CodUnitate AND d.[IDTipDocument]=5 AND d.[IDMijlocFix] = [AmixRegistru].[IDMijlocFix] AND d.[IDMijlocFix] IS NOT NULL
			AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
	IF @@ERROR <>0 GOTO Eroare  
	--------------------------------------------------------------------

	----Reactivare mijloc fix-------------------------------------------

	DECLARE @recalcul int
	SET @recalcul=0	
	Print('Reactivare mijloc fix')

	UPDATE [AmixRegistru] 
			SET [IDStare] = 5,
			[PunereInFunctiune] = d.[PunereInFunctiune]
			FROM [AmixDocumente] d
	WHERE  
			d.[Anul] = @An AND d.[Luna]=@Luna AND d.[CodUnitate]=@CodUnitate AND d.[IDTipDocument]=6 AND d.[IDMijlocFix] = [AmixRegistru].[IDMijlocFix] AND d.[IDMijlocFix] IS NOT NULL
			AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
	IF @@ERROR <>0 GOTO Eroare  
	--------------------------------------------------------------------
	IF @AnCurent*12 + @LunaCurenta <= @An*12+@Luna
	BEGIN
		PRINT('Calcul Amortizare lunara si pentru ultima luna')
		-- generare amortizare  luna curenta pentru mijloace fixe obisnuite
		INSERT INTO [AmixAmortizare]
		   ([IDMijlocFix],[Luna],[An],[ValoareInventar],[ValoareAmortizata],[ValoareRamasa]
		   ,[AmortizareAn],[AmortizareLuna],[DurataNormata],[LuniAmortizate])	
		SELECT [IDMijlocFix],
		@Luna,@An,ar.[ValoareInventar],ar.ValoareAmortizata,ar.[ValoareInventar] - ar.ValoareAmortizata,ar.[AmortizareAn],
		ar.[AmortizareLuna],[DurataAmortizare],
		CASE WHEN (DATEDIFF(month,[PunereInFunctiune],@UltimaZiLuna)-1) < 0 THEN 0
		ELSE (DATEDIFF(month,[PunereInFunctiune],@UltimaZiLuna)-1) END -- numarul de luni amortizare
		FROM [AmixRegistru] ar
		WHERE [CodUnitate]=@CodUnitate AND [RegimAmortizare]<>0 AND [Compus]<>1
		AND ar.[IDMijlocFix] NOT IN (SELECT [IDMijlocFix] FROM [AmixDocumente] d
			WHERE  d.[Anul] = @An AND d.[Luna]=@Luna AND d.[CodUnitate]=@CodUnitate AND d.[IDTipDocument]=3 
			AND (@IDDocument=0 OR [IDDocument]=@IDDocument))
		AND ar.[IDStare]=5 AND (YEAR([PunereInFunctiune])*100+MONTH([PunereInFunctiune]) < YEAR(@UltimaZiLuna)*100+MONTH(@UltimaZiLuna))
		IF @@ERROR <>0 GOTO Eroare  	


		--generare amortizare mijloace compuse
		-----------------------------------------------------------------------------------
		
		IF @Compus >= 1
		BEGIN
			Print('Prelucrare amortizare mijloace fixe compuse')
			
			INSERT INTO [AmixAmortizare]
			   ([IDMijlocFix],[Luna],[An],[ValoareInventar],[ValoareAmortizata],[ValoareRamasa]
			   ,[AmortizareAn],[AmortizareLuna],[DurataNormata],[LuniAmortizate])
			(SELECT b.[IDMFixPrincipal],
				@Luna,@An,
				r1.[ValoareInventar],
				SUM(ISNULL(a.[ValoareAmortizata],0)), 
				SUM(ISNULL(a.[ValoareRamasa],r2.[ValoareInventar])),
				SUM(ISNULL(a.[AmortizareAn],0)),
				SUM(ISNULL(a.[AmortizareLuna],0)),
				r1.[DurataAmortizare],
				CASE WHEN DATEDIFF(month,r1.[PunereInFunctiune],@UltimaZiLuna)-1 < 0 THEN 0
					 ELSE DATEDIFF(month,r1.[PunereInFunctiune],@UltimaZiLuna)-1 END
			FROM [AmixAnsamble] b
				LEFT OUTER JOIN [AmixAmortizare] a   ON a.[IDMijlocFix] = b.[IDMfixComponenta] AND a.[Luna] = @Luna AND a.[An]=@An
				JOIN [AmixRegistru] r2 ON b.[IDMfixComponenta]  = r2.[IDMijlocFix] 
				JOIN [AmixRegistru] r1 ON b.[IDMFixPrincipal] = r1.[IDMijlocFix]
			WHERE 
				(YEAR(r1.[PunereInFunctiune])*12 + MONTH(r1.[PunereInFunctiune])+1 <= @An*12+@luna) AND --din luna urmatoare datei de punere in functiune
				(@An*12+@Luna <= YEAR(r1.[PunereInFunctiune])*12+MONTH(r1.[PunereInFunctiune]) + r1.[DurataAmortizare])
				AND r1.[CodUnitate]=@CodUnitate  
				AND r2.[IDStare]=5
		   GROUP BY b.[IDMFixPrincipal],r1.[ValoareInventar],r1.[DurataAmortizare],r1.[PunereInFunctiune])

			IF @@ERROR <>0 GOTO Eroare  	

		END	
	END
	
	Print('actualizeaza valorile pentru documentele compuse')
	--actualizeaza valorile pentru documentele compuse
	UPDATE [AmixDocumente] SET [ValoareInventar] = a.[ValoareInventar],
	[ValoareAmortizata] = a.[ValoareAmortizata]
	FROM [AmixRegistru] a WHERE [AmixDocumente].[CodUnitate]=@CodUnitate
	AND [AmixDocumente].[IDMijlocFix]=a.[IDMijlocFix] AND [AmixDocumente].[Luna]=@Luna AND [AmixDocumente].[Anul]=@An
	AND [AmixDocumente].[Compus]=1
	IF @@ERROR <>0 GOTO Eroare

	

	----Blocheaza amortizarea mijloacelor fixe care pleaca din gestiune
	----trece mijloacele in mijloace iesite
	Print('trece mijloacele in mijloace iesite')
	UPDATE [AmixRegistru] 
			SET [IDStare] = 2,[InFolosinta]=0
			FROM [AmixDocumente] d
		WHERE  
			d.[Anul] = @An AND d.[Luna]=@Luna AND d.[CodUnitate]=@CodUnitate AND d.[IDTipDocument]=3 AND d.[IDMijlocFix] = [AmixRegistru].[IDMijlocFix] AND d.[IDMijlocFix] IS NOT NULL
			AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
	IF @@ERROR <>0 GOTO Eroare  
	-- Blocheaza amortizarile pentru mijloacele fixe care pleaca din gestiune
	Print('Blocheaza amortizarile pentru mijloacele fixe care pleaca din gestiune')
	UPDATE [AmixAmortizare] 
				SET [Blocat] = 1
				FROM [AmixDocumente] d
			WHERE  
				d.[Anul] = @An AND d.[Luna]=@Luna AND d.[CodUnitate]=@CodUnitate AND d.[IDTipDocument]=3 AND d.[IDMijlocFix] = [AmixAmortizare].[IDMijlocFix] AND d.[IDMijlocFix] IS NOT NULL
				AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
				AND [AmixAmortizare].[An]*100 + [AmixAmortizare].[Luna] >= @An*100+@Luna
	IF @@ERROR <>0 GOTO Eroare  

	Print('Transfer intern, schimbare gestionar')
	----Transfer intern, schimbare gestionar
	UPDATE [AmixRegistru] 
			SET [IDDepartament] = d.[IDDepartament],
			[IDgestionar] = d.[IDGestionar]
			FROM [AmixDocumente] d
		WHERE  
			d.[Anul] = @An AND d.[Luna]=@Luna AND d.[CodUnitate]=@CodUnitate AND d.[IDTipDocument]=4 AND d.[IDMijlocFix] = [AmixRegistru].[IDMijlocFix] AND d.[IDMijlocFix] IS NOT NULL
			AND (@IDDocument=0 OR [IDDocument]=@IDDocument)
	IF @@ERROR <>0 GOTO Eroare  




	Print('Inchidere Luna')
	----------- inchidere luna
	IF @InchideLuna=1
	BEGIN
		DECLARE @azi DateTime
		SET @azi = CAST(CAST(@An as varchar(4)) + '-' + cast(@Luna as varchar(2)) + '-01' AS DateTime)
		UPDATE [AmixSetup] SET [LunaCurenta]= MONTH(DATEADD(month,1,@azi)),[AnulCurent]=YEAR(DATEADD(month,1,@azi))
		WHERE [CodUnitate]=@CodUnitate
		IF @@ERROR <>0 GOTO Eroare 
		SET @Luna = MONTH(DATEADD(month,1,@azi))
		SET @An = YEAR(DATEADD(month,1,@azi))


		Print('Schimba stare mijloace amortizate')
		UPDATE [AmixRegistru] SET [AmortizareLuna] = 0,[AmortizareAn]=0,[IDStare]=1
		WHERE 
		[AmixRegistru].[CodUnitate]=@CodUnitate  AND [ValoareAmortizata] + AmortizareLuna >= [ValoareInventar] AND  [ValoareAmortizata] >= [ValoareInventar]
		AND [IDStare]=5
		IF @@ERROR <>0 GOTO Eroare


		Print('actualizeaza in registru valoarea de inventar si valoare amortizata in ultima luna')
		UPDATE [AmixRegistru] SET [AmortizareLuna] = [ValoareInventar] - [ValoareAmortizata],
		[ValoareAmortizata] = [ValoareInventar],[LuniAmortizate] = DurataAmortizare
		WHERE 
		[AmixRegistru].[CodUnitate]=@CodUnitate  AND [ValoareAmortizata] + AmortizareLuna >= [ValoareInventar] AND  [ValoareAmortizata] < [ValoareInventar]
		AND [IDStare]=5
		IF @@ERROR <>0 GOTO Eroare


		Print('actualizeaza in registru valoarea de inventar si valoare amortizata')
		--actualizeaza in registru valoarea de inventar si valoare amortizata
		UPDATE [AmixRegistru] SET 
		[ValoareAmortizata] = [ValoareAmortizata]+AmortizareLuna,[LuniAmortizate] = [LuniAmortizate]+1
		WHERE 
		[AmixRegistru].[CodUnitate]=@CodUnitate AND [ValoareAmortizata]+AmortizareLuna <[ValoareInventar]
		AND [IDStare]=5
		IF @@ERROR <>0 GOTO Eroare


	
		EXEC [amix_InchideLuna2] @Luna,@An,@CodUnitate,0,0;
	END 
	COMMIT

	---inchidere luna

	GOTO Gata
	DUBLURI:
		RAISERROR ('Exista mijloace fixe cu acelasi numar de inventar',16,-1)
		GOTO Gata

	EROARE:
		ROLLBACK
		RAISERROR ('Eroare la prelucrarea datelor',16,-1)
		GOTO Gata

		GATA:
END




GO








